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

SELECT COALESCE(field1, field2) AS the_field FROM my_table; would work if the fields were NULL. Are they really empty strings instead of being NULL? — wildplasser
– wildplasser, Commented Dec 5, 2012 at 12:34

Marcelo Zabani · Accepted Answer · 2012-12-05 12:41:06Z

84

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.

answered Dec 5, 2012 at 12:41

Marcelo Zabani

2,3191 gold badge20 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

czerny · Accepted Answer · 2015-03-03 11:59:04Z

53

Try COALESCE with NULLIF:

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

edited Mar 3, 2015 at 11:59

czerny

16.9k15 gold badges75 silver badges103 bronze badges

answered Dec 6, 2012 at 20:13

András Váczi

3,02225 silver badges30 bronze badges

Comments

David Aldridge · Accepted Answer · 2012-12-05 12:44:17Z

10

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

 select Coalesce(field1,field2)
 from   table;

answered Dec 5, 2012 at 12:44

David Aldridge

52.5k8 gold badges73 silver badges99 bronze badges

4 Comments

Marcelo Zabani Over a year ago

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

David Aldridge Over a year ago

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

Qwertie Over a year ago

Dude, no other language (including other SQLs) treats null as an empty string. (And that's not malarkey. Why should null exist in the first place if it's just an empty string?)

David Aldridge Over a year ago

I guess it's just what happens when a product predates the earliest standards for that type of product, and the standards take a different approach, and the product doesn't change to adopt the standard. Or something.

Collectives™ on Stack Overflow

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

3 Answers 3

Comments

Comments

4 Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

4 Comments

Related