Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.

-- count() counts ROW expressions that are NULL!
-- Based on table
CREATE TABLE tbl (col int);
INSERT INTO tbl VALUES (1), (null)
RETURNING *;


col


1

null


SELECT count(*)        AS "count(*)"       -- expected
     , count(col)      AS "count(col)"     -- expected
     , count(tbl)      AS "count(tbl)"     -- unexpected ...
     , count(tbl.*)    AS "count(tbl.*)"
     , count((tbl.*))  AS "count((tbl.*))"
     , count(ROW(col)) AS "count(ROW(col))"
FROM   tbl;


count(*)
count(col)
count(tbl)
count(tbl.*)
count((tbl.*))
count(ROW(col))


2
1
2
2
2
2


SELECT *
     , tbl IS NULL      AS "tbl IS NULL"
     , tbl.* IS NULL    AS "tbl.* IS NULL"
     , (tbl.*) IS NULL  AS "(tbl.*) IS NULL"
     , ROW(col) IS NULL AS "ROW(col) IS NULL"
     , col IS NULL      AS "col IS NULL"
FROM   tbl


col
tbl IS NULL
tbl.* IS NULL
(tbl.*) IS NULL
ROW(col) IS NULL
col IS NULL


1
f
f
f
f
f

null
t
t
t
t
t


-- Same with subquery
CREATE TABLE tbl2 (id serial PRIMARY KEY, col int);
INSERT INTO tbl2(col) VALUES (1), (null)
RETURNING *;


id
col


1
1

2
null


SELECT count(*)        AS "count(*)"       -- expected
     , count(col)      AS "count(col)"     -- expected
     , count(sub)      AS "count(sub)"     -- unexpected ...
     , count(sub.*)    AS "count(sub.*)"
     , count((sub.*))  AS "count((sub.*))"
     , count(ROW(col)) AS "count(ROW(col))"
FROM  (SELECT col FROM tbl2) sub;


count(*)
count(col)
count(sub)
count(sub.*)
count((sub.*))
count(ROW(col))


2
1
2
2
2
2


-- Same with composite values
CREATE TABLE tbl3 (composite tbl2); -- using ROW type from above
INSERT INTO tbl3 VALUES
  ((1, 2))  
, ((null, null))
RETURNING *;


composite


(1,2)

(,)


SELECT count(composite) FROM tbl3;  -- unexpected


count


2


SELECT *, composite IS NULL AS "composite IS NULL"
FROM   tbl3;


composite
composite IS NULL


(1,2)
f

(,)
t

col
1
null

id	col
1	1
2	null