CODEWITHSUNDEEP
postgresql
aubonphysics
aubonphysics

Reputation: 25

postgres query aggregate function does not display the null values

I have this table 

id | present
------------
A | false
B | null
C | null
D | false
E | false

and perform this query

SELECT array_agg(id)
FROM above_table
WHERE present IS NULL

I expect this to return a single row of

B C

But instead I get an empty row.

If I do

SELECT array_agg(id)
FROM above_table
WHERE present = 'false'

I get a row of 

A D E

Any thoughts on why IS NULL does not return the array?

Upvotes: 0

Views: 434

Answers (2)

vol7ron
vol7ron

Reputation: 42095

Works for me

DDL

CREATE TABLE above_table
    ("id" varchar(1), "present" boolean)
;

DML

INSERT INTO above_table
    ("id", "present")
VALUES
    ('A', false),
    ('B', NULL),
    ('C', NULL),
    ('D', false),
    ('E', false)
;

DQL

SELECT array_agg(id)
FROM   above_table
WHERE  present IS NULL;

Output

array_agg
B,C

I suspect something else is happening. Have you considered that the same table name may be used in multiple schemas and that you are pulling from the correct schema?

Upvotes: 1

Str.
Str.

Reputation: 1439

There is something wrong with your table definition or your test.

If you define column present as boolean and the rows with id B and C really are NULL, then your first query will give what you are expecting.

My console log:

strobel=# create table quest (id character, present boolean);
CREATE TABLE
strobel=# insert into quest values ('A',false);
INSERT 0 1
strobel=# insert into quest values ('D',false);
INSERT 0 1
strobel=# insert into quest values ('E',false);
INSERT 0 1
strobel=# insert into quest(id) values ('B');
INSERT 0 1
strobel=# insert into quest(id) values ('C');
INSERT 0 1
strobel=# select * from quest order by 1;
 id | present 
----+---------
 A  | f
 B  | 
 C  | 
 D  | f
 E  | f
(5 Zeilen)

strobel=# SELECT array_agg(id) FROM quest WHERE present IS NULL;
 array_agg                                                            
-----------
 {B,C}
(1 Zeile)

Upvotes: 1

Related Questions