COALESCE is not working in PostgreSQL -

when execute following code:

create table unsubscriptions (     email varchar(80) not null primary key,     unsubscribed boolean not null ); select coalesce(unsubscribed, false) unsubscriptions email = 'nnn'; 

i expect select never return null value, because there coalesce operation according postgresql docs returns first non-null value parameters (false in case).

however, on amazon rds instance, code above still returns null, if there no entries in table, should take second parameter.

why coalesce function not return false value?

i think key misunderstanding:

still returns null, if there no entries in table

if there no rows in table, there no rows in output of simple select. coalesce changes value in particular column, particular row, cannot create rows.

as others have pointed out, rows are in table constrained not null, coalesce not have effect.

what presumably seeing empty set in application you're querying data, resulting in behaviour similar set null in it. note sql result set 0 rows still have columns names , types (think of them headings of empty grid).

if want guarantee have at least 1 row, perhaps union dummy row onto results:

select unsubscribed unsubscriptions email = 'nnn' union select false unsubscribed 

this dummy row there real results, if there any, if didn't hassle of skipping in application code, you'd need cleverer solution.