Postgres 13

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?.

CREATE DOMAIN mydomain AS int NOT NULL;


CREATE TABLE foo1 (bar MYDOMAIN);
INSERT INTO foo1 VALUES (42);
1 rows affected

SELECT bar, pg_typeof(bar) FROM foo1;


bar
pg_typeof


42
mydomain


CREATE TABLE foo2(baz MYDOMAIN, qux MYDOMAIN);
INSERT INTO foo2 (baz, qux) VALUES (42, 42);
1 rows affected

SELECT bar, pg_typeof(bar) FROM foo1;


bar
pg_typeof


42
mydomain


SELECT baz, pg_typeof(baz), qux, pg_typeof(qux)
FROM foo2;


baz
pg_typeof
qux
pg_typeof


42
mydomain
42
mydomain


INSERT INTO foo2 (baz, qux)
SELECT f1.bar AS f1, f2.bar AS f2
FROM foo1 AS f1
LEFT JOIN foo1 AS f2
  ON false;
1 rows affected

SELECT baz, pg_typeof(baz), qux, pg_typeof(qux)
FROM foo2;


baz
pg_typeof
qux
pg_typeof


42
mydomain
42
mydomain

42
mydomain
null
mydomain


DROP TABLE foo1;
DROP TABLE foo2;
CREATE TABLE foo1 (bar INT NOT NULL);
CREATE TABLE foo2(baz INT NOT NULL, qux INT NOT NULL);
INSERT INTO foo1 (bar) VALUES (42);
1 rows affected

INSERT INTO foo2 (baz, qux) VALUES (42, 42);
1 rows affected

SELECT bar, pg_typeof(bar) FROM foo1;


bar
pg_typeof


42
integer


SELECT baz, pg_typeof(baz), qux, pg_typeof(qux)
FROM foo2;


baz
pg_typeof
qux
pg_typeof


42
integer
42
integer


INSERT INTO foo2 (baz, qux)
SELECT f1.bar AS f1, f2.bar AS f2
FROM foo1 AS f1
LEFT JOIN foo1 AS f2
  ON false;
ERROR:  null value in column "qux" of relation "foo2" violates not-null constraint
DETAIL:  Failing row contains (42, null).


SELECT baz, pg_typeof(baz), qux, pg_typeof(qux)
FROM foo2;


baz
pg_typeof
qux
pg_typeof


42
integer
42
integer