Postgres 12

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 temp1
(
    id   integer NOT NULL
        CONSTRAINT temp1_pk
            PRIMARY KEY,
    name text
);

INSERT INTO temp1
VALUES (1, 'abc'),
       (2, 'xyz');

CREATE TABLE temp2
(
    temp_id     integer
        CONSTRAINT temp2_temp1_id_fk
            REFERENCES temp1,
    name        text,
    description text
);

INSERT INTO temp2
VALUES (1, 'abc', 'first row'),
       (2, 'xyz', 'second row');
2 rows affected

2 rows affected

SELECT *
FROM temp1
         JOIN temp2 ON temp1.id = temp2.temp_id;


id
name
temp_id
name
description


1
abc
1
abc
first row

2
xyz
2
xyz
second row


DO
$do$
    BEGIN

        EXECUTE (
            SELECT CONCAT('CREATE VIEW temp_view AS SELECT ',
                          -- SELECT table1 columns here
                          (SELECT STRING_AGG('u1.' || column_name, ', ')
                           FROM information_schema.columns
                           WHERE table_name = 'temp1'
                             AND table_schema = 'public'
                              -- AND column_name NOT IN ('name')  -- not omitting for table1
                          ),
                          ', ',
                           -- SELECT table2 columns here
                          (SELECT STRING_AGG('u2.' || column_name, ', ')
                           FROM information_schema.columns
                           WHERE table_name = 'temp2'
                             AND table_schema = 'public'
                             AND column_name NOT IN ('name')),
                           -- Dynamically prepare the FROM and JOIN clauses
                          ' FROM temp1 u1 JOIN temp2 u2 ON u1.id = u2.temp_id'));

    END
$do$;
SELECT *
FROM temp_view;


id
name
temp_id
description


1
abc
1
first row

2
xyz
2
second row