How To Set Default Value: Postgres vs. MySQL
Default to current timestamp (It works in Postgres and MySQL)
create table t (
id int,
created_at timestamp default current_timestamp
);
insert into t(id) values(1);
Result:
select * from t;
id | created_at
----+----------------------------
1 | 2020-05-18 23:28:27.121457
(1 row)
Postgres specific syntax
create table t (
id int,
created_at timestamp default now()
);
This is because Postgres supports both function or constant as default values. So both now()
and current_timestamp
are legit values. But MySQL doesn’t.
Auto increment
Postgres
Postgres has a speical type serial
for this purpose:
create table t (
id serial,
created_at timestamp default now()
);
MySQL
MySQL requires auto_increment
to be a key:
create table t(
id int auto_increment,
created_at timestamp default current_timestamp,
primary key(id)
);
Change default value of an existing column
Postgres
Set default timestamp to one hour from now (It’s where Postgres Syntax shines):
alter table only t alter column created_at set default now() + interval '1 hour';
Remove default value completely:
alter table only t alter column created_at drop default;
MySQL
Remove default value:
alter table t modify column created_at timestamp;
Notice the difference with Postgres syntax in alter column
vs modify column
parts.
MySQL default value has to be a constant, so we can’t do the now plus interval easily in MySQL. We may implement the same function with triggers.
Good Resources
Above only scratches the surfaces of default values. Below are some links I found useful to dig deeper.
- MySQL data types
- PostgreSQL CREATE TABLE (See
default_expr
section) - StackOverflow
- Postgres vs. MySQL
Summary
This post summarizes two uses of SQL default values: current timestamp and auto increment.
There are some minor syntax differences between Postgres and MySQL. Postgres syntax is a bit more flexible because it supports using functions as default value.
If you want to learn more, sign up for my SQL course at BackToSQL.com.
Thank you for your read!