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

Solution 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.

Solution 2

Try COALESCE with NULLIF:

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

Solution 3

The ANSI SQL function Coalesce() is what you want.

 select Coalesce(field1,field2)
 from   table;

Comments

[Marcelo Zabani]

Apparently field1 may be an empty string, in which case the OP does not want its value, but field2's value instead.

[David Aldridge]

Ugh. Recently converted from Oracle and I don't like this empty-string-is-not-null malarkey.