Conditionally set a column to its default value in Postgres

Posted by Evgeny on Stack Overflow See other posts from Stack Overflow or by Evgeny
Published on 2010-04-20T04:38:14Z Indexed on 2010/04/20 4:43 UTC
Read the original article Hit count: 259

Filed under:
|

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?

© Stack Overflow or respective owner

Related posts about postgresql

Related posts about sql