10

With PostgreSQL (I'm using the version 9.1) is it possible to do mass update with a single query a column value with the value of another column, but if the other column value is null, to use the value of a third column, and if the third one is absent to use the current datetime (all the column have type timestamp)

I need to change

columnA columnB columnC
null    foo     bar
null    null    baz
null    null    null

to

columnA columnB columnC
foo     foo     bar
baz     null    baz
quz     null    null

where quz is the current datetime.

2 Answers 2

15

You can use COALESCE() function to do the update:

UPDATE ...
SET columnA = COALESCE(columnB, columnC, now()); 

COALESCE will return the first non-null value in the list that you provide.

12

One can use COALESCE to provide defaults instead of possibly NULL values. It is a variadic function, so it can take any number of parameters. The return value will be the first non-NULL parameter (if there is none, it still returns NULL).

Using it, you UPDATE might look like:

UPDATE your_table
SET columnA = COALESCE(columnB, columnC, 'quz');

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.