PostgreSQL COALESCE function returns Null even though value exists

Question

I've got the following table (nodes) in a PostgreSQL DB and I'm looking to extract either the http OR https information. I am using COALESCE.

SELECT COALESCE(http,https) 
FROM oig.nodes 
WHERE owner_name = %s 
  AND node_type = COALESCE('full','api')

|---------------------|------------------|------------------|------------------|
|      owner          |     node_type    |       http       |      https       |
|---------------------|------------------|------------------|------------------|
|          test123    |         full     | http://1.1.1.1   |                  |
|---------------------|------------------|------------------|------------------|
|          testabc    |         api      |                  | http://1.1.1.2   |
|---------------------|------------------|------------------|------------------|
|          testabc    |         seed     |                  |                  |
|---------------------|------------------|------------------|------------------|

This works, but only if the node_type = full, if the node_type = api, it returns None.

Maybe it's not a null value, but an empty string (''). Try nullif(http_node_url, '') — user330315
– user330315, Commented Aug 11, 2020 at 8:43
I check definitely is NULL value. I forgot to mention that there could be two or more owners with the same name. — Ankh2054
– Ankh2054, Commented Aug 11, 2020 at 8:48
As you see now, "it returns None" is a bit vague. We thought that you get a result row in which the value is null. Now it seems you are not getting any row at all. Always describe what happens very precisely, so we don't have to guess. — Thorsten Kettner
– Thorsten Kettner, Commented Aug 11, 2020 at 9:20

Tomáš Záluský · Accepted Answer · 2020-08-11 08:49:35Z

2

COALESCE('full','api') is equivalent to 'full' because 'full' is definitely not null.

answered Aug 11, 2020 at 8:49

Tomáš Záluský

12.3k4 gold badges44 silver badges74 bronze badges

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

8 Comments

Ankh2054 Over a year ago

yes works if owner = test123, but if owner = testabc, it returns None.

Tomáš Záluský Over a year ago

because none row matches AND condition. Didn't you rather mean AND node_type in ('full','api')?

Ankh2054 Over a year ago

just tried that and same issue - SELECT COALESCE(http_node_url,https_node_url),owner_name FROM oig.nodes WHERE owner_name = %s AND node_type in ('full','api')

Tomáš Záluský Over a year ago

weird... in can be broken to two expressions separated by OR. Perhaps some whitespace or parenthesising issue... (may be try to extract it to selfstanding query, your column names don't match those in your ascii-art table)

Ankh2054 Over a year ago

When I try SELECT COALESCE(http_node_url,https_node_url),owner_name FROM oig.nodes WHERE owner_name = %s AND node_type = 'full' OR node_type = 'api' - it then only returns the first row from the table no matter what owner_)name I pass.

|

Collectives™ on Stack Overflow

PostgreSQL COALESCE function returns Null even though value exists

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related