row_is_null.out

SELECT ROW(NULL) IS NULL;
 ?column? 
----------
 t
(1 row)

SELECT ROW(ROW(NULL)) IS NULL;
 ?column? 
----------
 t
(1 row)

SELECT ROW(ROW(ROW(NULL))) IS NULL;
 ?column? 
----------
 f
(1 row)

SELECT RECORD(NULL) IS NULL;
 ?column? 
----------
 t
(1 row)

SELECT RECORD(RECORD(NULL)) IS NULL;
 ?column? 
----------
 t
(1 row)

SELECT RECORD(RECORD(RECORD(NULL))) IS NULL;
 ?column? 
----------
 t
(1 row)

DROP TABLE IF EXISTS tt;
DROP TABLE
CREATE TABLE tt (x INT);
CREATE TABLE
INSERT INTO tt VALUES(NULL);
INSERT 0 1
SELECT ROW(x) IS NULL FROM tt;
 ?column? 
----------
 t
(1 row)

SELECT ROW(ROW(x)) IS NULL FROM tt;
 ?column? 
----------
 t
(1 row)

SELECT ROW(ROW(ROW(x))) IS NULL FROM tt;
 ?column? 
----------
 f
(1 row)

SELECT coalesce(ROW(NULL,NULL), ROW('no', 'bueno'));
 coalesce 
----------
 (,)
(1 row)

SELECT CASE WHEN ROW(NULL,NULL) IS NULL THEN ROW('no', 'bueno') END;
    case    
------------
 (no,bueno)
(1 row)

DROP TABLE IF EXISTS test3;
DROP TABLE
DROP TABLE IF EXISTS test2;
DROP TABLE
CREATE TABLE test2 (x INT);
CREATE TABLE
CREATE TABLE test3(x test2 NOT NULL);
CREATE TABLE
INSERT INTO test3 VALUES (null);
ERROR:  null value in column "x" violates not-null constraint
DETAIL:  Failing row contains (null).
INSERT INTO test3 VALUES (row(null));
INSERT 0 1
DO LANGUAGE plpgsql $$
DECLARE
r RECORD;
BEGIN

DROP TABLE IF EXISTS test;
CREATE TABLE test (x INT, y INT);

INSERT INTO test VALUES (1, NULL), (NULL, 1), (NULL, NULL);
FOR r IN SELECT * FROM test
LOOP
IF (r IS NULL)
THEN RAISE NOTICE 'true';
ELSE RAISE NOTICE 'false';
END IF;
END LOOP;
END;
$$;
NOTICE:  false
NOTICE:  false
NOTICE:  true
DO
DO LANGUAGE plpgsql $$
DECLARE
r RECORD;
BEGIN

SELECT NULL INTO r;
IF (r IS NULL)
THEN RAISE NOTICE 'true';
ELSE RAISE NOTICE 'false';
END IF;

SELECT ROW(NULL) INTO r;
IF (r IS NULL)
THEN RAISE NOTICE 'true';
ELSE RAISE NOTICE 'false';
END IF;

END;
$$;
NOTICE:  true
NOTICE:  false
DO