Postgres default value NULL and empty string

Question

Usually the default value is used if the value to insert is NULL. Is there a way to tell postgres that it should use the default value also on empty string ('')?

Answer 1

No, you can't do so directly.

The only valid ways to specify a default value are:

• To omit the column from an INSERT column-list; or
• To explicitly specify the keyword DEFAULT for a column in an INSERT or UPDATE.

Neither NULL or '' are valid ways to request a default value for a column.

If you wish to replace NULL or '' with the default you must use a BEFORE ... FOR EACH ROW trigger to do so.

Making a generic one isn't especially trivial, as you can't use the DEFAULT keyword when assigning to the NEW tuple within a trigger; you'd instead have to look up the system catalogs for the default expression.

Of course, you can always just repeat the default expression in the trigger if you know it and don't mind coding a trigger specific to it.

All in all, it's way better to fix the application so it follows the standard and doesn't expect the DB to replace NULL or '' with a default value.