Why does postgres group null values?

Question

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

Barrel

They are not really "equal" in the group. It's just a group for anything that can't be compared with = (which in this case means all null values) – Tallow

Because the grouping is not defined as equality within the group, but as difference with other groups. – Small

is this behaviour documented anywhere? – Barrel

ON (a.value IS NOT DISTINCT FROM b.value) would actually give you 4 result rows. – Small

postgresql.org/message-id/… - but I'd like to see where it is sayd that standard is clear about each of these things separately. It absolutely says that nulls should be grouped together, and it absolutely says that the comparison operator should not. – Hetero

Answer 1

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.

Tabatha

but why does it match while grouping? – Barrel

which function is used to compare while grouping? or is null==null hardcoded while grouping? is there any doc about hat? – Barrel

@Small but the question is why are they group together if they arent "equal" the answer is as OP suggested is "hard coded" – Tabatha

@JuanCarlosOropeza : yes I understand that. I should have commented under the OQ. – Small

Answer 2

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.

Arnettaarnette