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);


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 has a speical type serial for this purpose:

 create table t (
  id serial,
  created_at timestamp default now()


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


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;


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.


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

Thank you for your read!

By Junji Zhi on May 15, 2020