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 users (id serial, info text, metadata_id int);
create table metadata(id serial, data text);

CREATE TABLE

CREATE TABLE

insert into users (info) values ('foo'), ('bar')

INSERT 0 2

select * from users;

id
info
metadata_id

1
foo
null

2
bar
null

SELECT 2

with 
	candidates as (
		select u.*, nextval(pg_get_serial_sequence('metadata', 'id')) new_metadata_id
		from users u
	),
	inserted as (
		insert into metadata (id, data) overriding system value
		select new_metadata_id, info from candidates
	)
update users u
set metadata_id = c.new_metadata_id
from candidates c
where c.id = u.id

UPDATE 2

select * from users;

id
info
metadata_id

1
foo
1

2
bar
2

SELECT 2

select * from metadata

id
data

1
foo

2
bar

SELECT 2