Postgres 16

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 info (
  info_id serial PRIMARY KEY
, info_text TEXT);

CREATE TABLE product (
  prod_id serial PRIMARY KEY
, info_id int UNIQUE REFERENCES info  -- works with FK & UNIQUE, too!
, description TEXT UNIQUE
, price int
);


INSERT INTO product (description, price) VALUES
  ('pen', 1)
, ('notebook', 2)
, ('ruller', 3)
;

CREATE TABLE



CREATE TABLE



INSERT 0 3


TABLE product;
TABLE info;


prod_id
info_id
description
price


1
null
pen
1

2
null
notebook
2

3
null
ruller
3



SELECT 3




info_id
info_text



SELECT 0


WITH upd AS (
   UPDATE product
   SET    info_id = nextval(pg_get_serial_sequence('info', 'info_id'))
   WHERE  info_id IS NULL
   RETURNING info_id
   )
INSERT INTO info (info_id)
SELECT info_id
FROM   upd;

INSERT 0 3


TABLE product;
TABLE info;


prod_id
info_id
description
price


1
1
pen
1

2
2
notebook
2

3
3
ruller
3



SELECT 3




info_id
info_text


1
null

2
null

3
null



SELECT 3

info_id	info_text
1	null
2	null
3	null