Should not we look for database to be able to do online, efficient non locking addition of column with any default value, not just NULL rather than application to have a complicated and fragile logic ?

▲

avg_dev a day ago | parent [-]

I believe PostgreSQL does this since v11, which was released in 2018: (current is v17)

> Many other useful performance improvements, including the ability to avoid a table rewrite for ALTER TABLE ... ADD COLUMN with a non-null column default

https://www.postgresql.org/docs/release/11.0/

I think there is some restriction there, like the default can't be "volatile" - I can't remember the precise definition here but I think current_timestamp would be volatile, but any static value would not.

▲

tudorg a day ago | parent [-]

That is correct, for non-volatile default values Postgres is quick, which means that it is generally a safe operation.

Also interesting, `now()` is non-volatile because it's defined as "start of the transaction". So if you add a column with `DEFAULT now()` all rows will get the same value. But `timeofday()` is not volatile, so `DEFAULT timeofday()` is going to lock the table for a long time. A bit of a subtle gotcha.

▲

avg_dev a day ago | parent [-]

Thanks for the info. One minor point:

> But `timeofday()` is not volatile, so `DEFAULT timeofday()` is going to lock the table for a long time.

Perhaps the “not” was a typo?

	▲	tudorg 14 hours ago \| parent [-]
		Ah yes, it was a typo, sorry.