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.

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!


Email Newsletter

I write about code and entrepreneurship. Sign up and get my updates straight to your inbox!



Junji Zhi

Senior Software Engineer.