-- Generated from nulls.pdf at https://momjian.us/presentations
-- This is intended to be run by psql so backslash commands are processed.

-- setup
\pset footer off
\pset null (null)




CREATE TABLE nulltest (x INTEGER, y INTEGER);

INSERT INTO nulltest VALUES (1, NULL);

SELECT * FROM nulltest;

INSERT INTO nulltest (x) VALUES (2);

SELECT * FROM nulltest;

CREATE TABLE nulltest2 (x INTEGER NOT NULL, y INTEGER NOT 
NULL);

INSERT INTO nulltest2 VALUES (3, NULL);

INSERT INTO nulltest2 (x) VALUES (4);

SELECT NULL + 1;

SELECT NULL || 'a';

SELECT 'b' || NULL;

CREATE TABLE inctest (x INTEGER);

INSERT INTO inctest VALUES (30), (40), (NULL);

SELECT x + 1 FROM inctest;

SELECT NULL = 1;

SELECT NULL = '';

SELECT NULL = NULL;

SELECT NULL < NULL + 1;

SELECT 1
WHERE true;

SELECT 1
WHERE false;

SELECT 1
WHERE NULL;

SELECT true AND NULL;

SELECT NOT NULL;

SELECT * FROM inctest;

SELECT * FROM inctest WHERE x >= 0;

SELECT * FROM inctest WHERE x < 0;

SELECT * FROM inctest WHERE x < 0 OR x >= 0;

SELECT * FROM inctest WHERE x <> 0;

SELECT 1 <> 2 AND 1 <> 3;

SELECT 1 <> 2 AND 1 <> 3 AND 1 <> NULL;

SELECT 'a' IN (SELECT NULL::text);

SELECT 'a' NOT IN (SELECT NULL::text);

SELECT 'a' IN (VALUES ('a'), (NULL));

SELECT 'a' NOT IN (VALUES ('a'), (NULL));

SELECT 'a' IN (VALUES ('b'), (NULL));

SELECT 'a' NOT IN (VALUES ('b'), (NULL));

SELECT 'a' = 'b' OR 'a' = NULL;

SELECT 'a' <> 'b' AND 'a' <> NULL;

SELECT EXISTS (SELECT 1);

SELECT EXISTS (SELECT NULL);

SELECT NOT EXISTS (SELECT NULL);

SELECT NULL = NULL;

SELECT NULL IS NULL;

SELECT NULL IS NOT NULL;

SELECT * FROM inctest;

SELECT * FROM inctest WHERE x IS NULL;

SELECT * FROM inctest WHERE x IS NOT NULL;

SELECT 1 IS NOT DISTINCT FROM 1;

SELECT NULL = 1;

SELECT NULL IS NOT DISTINCT FROM 1;

SELECT NULL IS NOT DISTINCT FROM NULL;

SELECT * FROM inctest WHERE x IS DISTINCT FROM 30;

SELECT * FROM inctest WHERE x IS NOT DISTINCT FROM 30;

CREATE TABLE disttest (x INTEGER, y INTEGER);

INSERT INTO disttest VALUES (1, 1), (2, 3), (NULL, NULL);

SELECT * FROM disttest where x IS NOT DISTINCT FROM y;

SELECT * FROM (VALUES (NULL), (2), (1), (NULL)) AS v(x) ORDER 
BY 1;

SELECT * FROM (VALUES (NULL), (2), (1), (NULL)) AS v(x) ORDER 
BY 1 NULLS FIRST;

CREATE TABLE uniqtest (x INTEGER);

CREATE UNIQUE INDEX i_uniqtest ON uniqtest (x);

INSERT INTO uniqtest VALUES (1), (NULL), (NULL);

SELECT * FROM uniqtest;

CREATE TABLE checktest (x INTEGER CHECK (x > 0));

INSERT INTO checktest VALUES (1), (NULL);

SELECT * FROM CHECKTEST;

ALTER TABLE checktest ALTER COLUMN x SET NOT NULL;

CREATE TABLE aggtest (x INTEGER);

INSERT INTO aggtest VALUES (7), (8), (NULL);

SELECT COUNT(*), COUNT(x), SUM(x), MIN(x), MAX(x), AVG(x) FROM 
aggtest;

DELETE FROM aggtest;

SELECT COUNT(*), COUNT(x), SUM(x), MIN(x), MAX(x), AVG(x) FROM 
aggtest;

DELETE FROM aggtest;

INSERT INTO aggtest VALUES (7), (8), (NULL), (NULL);

SELECT x, COUNT(*), COUNT(x), SUM(x), MIN(x), MAX(x), AVG(x)
FROM aggtest
GROUP BY x
ORDER BY x;

SELECT COALESCE(NULL, 0);

SELECT COALESCE(NULL, 'I am null.');

CREATE TABLE nullmaptest (x TEXT);

INSERT INTO nullmaptest VALUES ('f'), ('g'), (NULL);

SELECT x, COALESCE(x, 'n/a') FROM nullmaptest;

SELECT 'a' || COALESCE(NULL, '') || 'b';

SELECT SUM(x), COALESCE(SUM(x), 0) FROM aggtest;

DELETE FROM nullmaptest;

INSERT INTO nullmaptest VALUES ('f'), ('g'), ('n/a');

SELECT x, NULLIF(x, 'n/a') FROM nullmaptest;

SELECT NULLIF('n/a', COALESCE(NULL, 'n/a'));

SELECT NULL::INTEGER[] IS NULL;

SELECT '{}'::INTEGER[] IS NULL;

SELECT '{NULL}'::INTEGER[] IS NULL;

SELECT ROW() IS NULL, ROW() IS NOT NULL;

SELECT ROW(NULL) IS NULL;

SELECT ROW(NULL, NULL) IS NULL;

SELECT ROW(NULL, 1) IS NULL;

SELECT ROW(NULL, 1) IS NOT NULL;

SELECT ROW(1, 2) IS NOT NULL;

CREATE TABLE emptytest (x INTEGER);

SELECT * from emptytest;

SELECT (SELECT * from emptytest);

SELECT (SELECT * from emptytest) IS NULL;

