Conditionally set a column to its default value in Postgres
- by Evgeny
I've got a PostgreSQL 8.4 table with an auto-incrementing, but nullable, integer column. I want to update some column values and, if this column is NULL then set it to its default value (which would be an integer auto-generated from a sequence), but I want to return its value in either case. So I want something like this:
UPDATE mytable
SET incident_id = COALESCE(incident_id, DEFAULT), other = 'somethingelse'
WHERE ...
RETURNING incident_id
Unfortunately, this doesn't work - it seems that DEFAULT is special and cannot be part of an expression. What's the best way to do this?