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
postgresql
|sql
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