PostgreSQL: Select one of two fields depending on which is empty

Question

Hello I have a query where I want to select the value of one of two fields depending if one is empty.

field1 and field2 

I want to select them as complete_field

IF field1 is empty, then complete_field is field2 ELSE complete_field is field1 

In php it would be done like:

$complete_field = $field1 == '' ? $field2 : $field1; 

How would I do this in PostgreSQL?

I tried:

SELECT (IF field1 = '' THEN field2 ELSE field1) AS complete_field FROM table 

But it doesnt work.

Please help me :) Thanks

Answer 1

Use CASE WHEN .. THEN .. ELSE .. END, e.g.:

SELECT  (CASE WHEN (field1 IS NULL OR field1 = '') THEN field2 ELSE field1 END) FROM table; 

Check it out at the official docs.

Answer 2

Try COALESCE with NULLIF:

SELECT COALESCE(NULLIF(field1, ''), field2) AS the_field FROM my_table;