postgres query aggregate function does not display the null values

Question

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?

Column | Type | Modifiers ----------------+--------------------------------+------------------------ present | boolean | — aubonphysics
– aubonphysics, Commented Apr 18, 2015 at 16:35

Str. · Accepted Answer · 2015-04-18 16:49:22Z

1

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)

edited Apr 18, 2015 at 16:49

answered Apr 18, 2015 at 16:42

Str.

1,43911 silver badges14 bronze badges

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

2 Comments

aubonphysics Over a year ago

This is what I see in the database. But I don't get the results back. I get an empty array.

Pavel Stehule Over a year ago

I can confirm this correct expected behave. Maybe your NULL is not NULLs. Try to set format for NULLs by \pset null '[*** NULL ***]'.

vol7ron · Accepted Answer · 2015-04-26 16:30:22Z

1

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?

edited Apr 26, 2015 at 16:30

answered Apr 18, 2015 at 16:52

vol7ron

42.3k22 gold badges126 silver badges178 bronze badges

3 Comments

aubonphysics Over a year ago

I have not considered that. Looking into it.

Str. Over a year ago

@aubonphysics The same thing I did. Everything okay. You have to check your table. Or you are in the wrong database. Or ...

vol7ron Over a year ago

@Str. I believe his comment is in response to my last statement regarding the schema, not the table/query stuff.

Collectives™ on Stack Overflow

postgres query aggregate function does not display the null values

2 Answers 2

2 Comments

Works for me

DDL

DML

DQL

Output

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Works for me

DDL

DML

DQL

Output

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related