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 notecards (
notecard_id int not null generated always as identity primary key,
title text not null,
reference text not null,
note_type text not null,
main_text text not null
);
create table tags_notecard (
tag_id int not null,
notecard_id int not null references notecards(notecard_id),
primary key (tag_id, notecard_id)
);
CREATE TABLE
CREATE TABLE
with invars as (
select '{
"title": "title",
"reference": "1213",
"noteType": "type_1",
"tags": [3, 4],
"text": "Lorem ipsum dolor sit amet."
}'::jsonb as req_body
), insert_notecard as (
insert into notecards (title, reference, note_type, main_text)
select req_body->>'title', req_body->>'reference', req_body->>'noteType',
req_body->>'text'
from invars
returning notecard_id
), insert_tags as (
insert into tags_notecard (tag_id, notecard_id)
select t.tag_id::int, n.notecard_id
from insert_notecard n
cross join invars i
cross join lateral
jsonb_array_elements_text(i.req_body->'tags') t(tag_id)
returning *
)
select * from insert_tags;
select * from notecards;
| tag_id | notecard_id |
|---|---|
| 3 | 1 |
| 4 | 1 |
SELECT 2
| notecard_id | title | reference | note_type | main_text |
|---|---|---|---|---|
| 1 | title | 1213 | type_1 | Lorem ipsum dolor sit amet. |
SELECT 1