Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

like image 538
wutzebaer Avatar asked Oct 18 '22 03:10

wutzebaer


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.

like image 179
Juan Carlos Oropeza Avatar answered Nov 15 '22 08:11

Juan Carlos Oropeza