felixge 9 days ago

> 1: Never add a column with a default value

This only applies to Postgres versions < 11. See https://blog.2ndquadrant.com/add-new-table-column-default-va... which was released after this article was written.

  • Lewton 9 days ago

    I ran into this last week when wanting to upgrade a column to non-null and discovering we were running Postgres 9.something

    It made me curious, since the solution is so simple and fairly obvious, and it covers a common use case. How come it’s taken so long to be implemented? Similarly, it wasn’t added in oracle until version 11, so I must be missing something

    • pilif 9 days ago

      Mainly because it's somewhat of a convenience feature for which a viable workaround exists:

      do a normal update, concurrently add a conditional `where ... is null` index, repeat in a loop (begin transaction, normal update, add constraint, commit) until it stops failing.

      This will still take a lock, but thanks to most rows already updated and because of the conditional index, the lock will only last a very short amount of time.

    • ddorian43 9 days ago

      Because someone didn't need + implement it.