Why does postgres group null values?

user_1131857 0
Viewed 4695
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

group-by null postgresql
1 Answers

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.

user_34701780
Related