Postgres 11

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 TABLE fields (active boolean, core boolean, id integer);

INSERT INTO fields
VALUES
(false,true,3419),
(false,true,3420)
;

TABLE fields;

CREATE TABLE

INSERT 0 2

active
core
id

f
t
3419

f
t
3420

SELECT 2

SELECT pg_typeof(NULL);

pg_typeof

unknown

SELECT 1

SELECT pg_typeof(core) FROM (
    VALUES (NULL)
) new_values (core);

pg_typeof

text

SELECT 1

SELECT (x).* FROM (VALUES ((TRUE, NULL, 1234)::fields)) t(x);

active
core
id

t
null
1234

SELECT 1

UPDATE fields AS t set active = (x).active, core = (x).core
FROM ( VALUES
           ((true, NULL, 3419)::fields),
           ((false, NULL, 3420)::fields)
     ) AS new_values(x) WHERE (x).id = t.id;

UPDATE 2

TABLE fields;

active
core
id

t
null
3419

f
null
3420

SELECT 2