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 posts (
id serial PRIMARY KEY
, post_data text NOT NULL
);
CREATE TABLE hashtags (
id serial PRIMARY KEY
, value text UNIQUE NOT NULL -- MUST be UNIQUE (!)
);
CREATE TABLE post_hashtags (
-- id SERIAL PRIMARY KEY, -- optional!
post_id int NOT NULL REFERENCES posts
, hashtag_id int NOT NULL REFERENCES hashtags
, PRIMARY KEY (post_id, hashtag_id) -- recommended!
);
INSERT INTO posts(post_data) VALUES ('post1');
INSERT INTO hashtags (value) VALUES ('hashtag1'), ('hashtag2'), ('hashtag3');
CREATE TABLE
CREATE TABLE
CREATE TABLE
INSERT 0 1
INSERT 0 3
WITH input(post_data, tags) AS ( -- provide single data row with array of tags
VALUES ('post2', '{hashtag1, hashtag2, hashtag4}'::text[]) -- single post!
)
, tag_set AS ( -- unnest tags - may be empty/missing (?)
SELECT unnest(i.tags) AS value
FROM input i
)
, ins_p AS (
INSERT INTO posts (post_data)
SELECT i.post_data
FROM input i
RETURNING id AS post_id
)
, ins_h AS (
INSERT INTO hashtags (value)
SELECT t.value
FROM tag_set t
WHERE NOT EXISTS (SELECT FROM hashtags h WHERE h.value = t.value) -- optional to avoid burning lots of serial IDs
ON CONFLICT (value) DO NOTHING
RETURNING id AS hashtag_id
)
INSERT INTO post_hashtags
( post_id, hashtag_id)
SELECT p.post_id, t.hashtag_id
FROM ins_p p
CROSS JOIN ( -- insert only if tags were entered
TABLE ins_h -- new tags
UNION ALL
SELECT h.id AS hastag_id -- pre-existing tags
FROM tag_set t
JOIN hashtags h USING (value)
) t
RETURNING *;
| post_id | hashtag_id |
|---|---|
| 2 | 4 |
| 2 | 1 |
| 2 | 2 |
INSERT 0 3
TABLE posts;
TABLE hashtags;
TABLE post_hashtags;
| id | post_data |
|---|---|
| 1 | post1 |
| 2 | post2 |
SELECT 2
| id | value |
|---|---|
| 1 | hashtag1 |
| 2 | hashtag2 |
| 3 | hashtag3 |
| 4 | hashtag4 |
SELECT 4
| post_id | hashtag_id |
|---|---|
| 2 | 4 |
| 2 | 1 |
| 2 | 2 |
SELECT 3
-- no tags for new post
WITH input(post_data, tags) AS (
VALUES ('post3', '{}'::text[]) -- single post, no tags !!!
)
, tag_set AS ( -- unnest tags - actually empty
SELECT unnest(i.tags) AS value
FROM input i
)
, ins_p AS (
INSERT INTO posts (post_data)
SELECT i.post_data
FROM input i
RETURNING id AS post_id
)
, ins_h AS (
INSERT INTO hashtags (value)
SELECT t.value
FROM tag_set t
WHERE NOT EXISTS (SELECT FROM hashtags h WHERE h.value = t.value)
ON CONFLICT (value) DO NOTHING
RETURNING id AS hashtag_id
)
INSERT INTO post_hashtags
( post_id, hashtag_id)
SELECT p.post_id, t.hashtag_id
FROM ins_p p
CROSS JOIN ( -- no tags were entered
TABLE ins_h -- new tags
UNION ALL
SELECT h.id AS hastag_id -- pre-existing tags
FROM tag_set t
JOIN hashtags h USING (value)
) t
RETURNING *;
| post_id | hashtag_id |
|---|
INSERT 0 0
TABLE posts;
TABLE hashtags;
TABLE post_hashtags;
| id | post_data |
|---|---|
| 1 | post1 |
| 2 | post2 |
| 3 | post3 |
SELECT 3
| id | value |
|---|---|
| 1 | hashtag1 |
| 2 | hashtag2 |
| 3 | hashtag3 |
| 4 | hashtag4 |
SELECT 4
| post_id | hashtag_id |
|---|---|
| 2 | 4 |
| 2 | 1 |
| 2 | 2 |
SELECT 3