CODEWITHSUNDEEP
postgresqlgroup-bynull
wutzebaer
wutzebaer

Reputation: 14865

Why does postgres group null values?

CREATE TEMP TABLE wirednull (
    id bigint NOT NULL,
    value bigint,
    CONSTRAINT wirednull_pkey PRIMARY KEY (id)
);
INSERT INTO wirednull (id,value) VALUES (1,null);
INSERT INTO wirednull (id,value) VALUES (2,null);

SELECT value FROM wirednull GROUP BY value;

Returns one row, but i would expect two rows since

SELECT * 
FROM wirednull a 
LEFT JOIN wirednull b 
  ON (a.value = b.value)

does not find any joins, because null!=null in postgres

Upvotes: 6

Views: 9096

Answers (2)

ggonzalez
ggonzalez

Reputation: 11

This should do what you want to do:

SELECT value FROM wirednull GROUP BY COALESCE(value, id), value;

COALESCE returns the first value if not null, otherwise it returns the second (or the third, fourth, and so on as long as the previous values are null).

The addition of value in the group by clause is only to allow you selecting it.

There are already many answers to explain 'why' they're grouped together, so here is a way to avoid grouping them together.

Upvotes: 1

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48197

According to SQL wikipedia :

When two nulls are equal: grouping, sorting, and some set operations

Because SQL:2003 defines all Null markers as being unequal to one another, a special definition was required in order to group Nulls together when performing certain operations. SQL defines "any two values that are equal to one another, or any two Nulls", as "not distinct".[20] This definition of not distinct allows SQL to group and sort Nulls when the GROUP BY clause (and other keywords that perform grouping) are used.

This wasn't the question:

Because null = null or something = null return unknown not true/false

So:

ON (a.value = b.value)

Doesn't match.

Upvotes: 3

Related Questions