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 TableA ("user" VARCHAR(5), quantity INTEGER);

INSERT INTO TableA ("user", quantity) VALUES
  ('mike', '100'),
  ('bob', '200'),
  ('alice', '50');

CREATE TABLE TableB ("group" VARCHAR(5), "user" VARCHAR(5), quantity INTEGER);

INSERT INTO TableB ("group", "user", quantity) VALUES
  ('ace', 'mike', '10'),
  ('beta', 'mike', '15'),
  ('ace', 'bob', '2'),
  ('omega', 'bob', '15'),
  ('omega', 'alice', '25');

CREATE TABLE



INSERT 0 3



CREATE TABLE



INSERT 0 5


ALTER TABLE TableB ADD CONSTRAINT un_group_user UNIQUE("group", "user");

ALTER TABLE


INSERT INTO TableB("group", "user", quantity)
SELECT 'beta', "user", quantity / 2.0
FROM TableA
ON CONFLICT("group", "user") DO UPDATE SET
quantity = EXCLUDED.quantity;

INSERT 0 3


SELECT * FROM TableB;


group
user
quantity


ace
mike
10

ace
bob
2

omega
bob
15

omega
alice
25

beta
mike
50

beta
bob
100

beta
alice
25



SELECT 7


INSERT INTO TableB("group", "user", quantity)
SELECT 'xyz', "user", quantity / 2.0
FROM TableA
ON CONFLICT("group", "user") DO UPDATE SET
quantity = EXCLUDED.quantity;

INSERT 0 3


SELECT * FROM TableB;


group
user
quantity


ace
mike
10

ace
bob
2

omega
bob
15

omega
alice
25

beta
mike
50

beta
bob
100

beta
alice
25

xyz
mike
50

xyz
bob
100

xyz
alice
25



SELECT 10

group	user	quantity
ace	mike	10
ace	bob	2
omega	bob	15
omega	alice	25
beta	mike	50
beta	bob	100
beta	alice	25