Postgres 9.6

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 questions (
  question_id serial PRIMARY KEY
, title text NOT NULL
, body text
, userid int
, categoryid int
);

CREATE TABLE tags (
  tag_id serial PRIMARY KEY
, tag text NOT NULL UNIQUE
);

CREATE TABLE questiontags (
  question_id int REFERENCES questions
, tag_id      int REFERENCES tags
, PRIMARY KEY(question_id, tag_id)
);
WITH input_data(body, userid, title, categoryid, tags) AS (
   VALUES ('some_body2', 3, 'some_title2', 5, '{foo1, bar, baz}')
   )
 , input_tags AS (
      SELECT DISTINCT tag     -- fold duplicates
      FROM   input_data, unnest(tags::text[]) tag
      )
 , q AS (  -- insert into questions
   INSERT INTO questions
         (body, userid, title, categoryid)
   SELECT body, userid, title, categoryid
   FROM   input_data
   RETURNING question_id
   )
 , t AS (  -- insert into tags
   INSERT INTO tags (tag)
   TABLE  input_tags  -- short for: SELECT * FROM input_tags
   ON     CONFLICT (tag) DO NOTHING  -- only new tags
   RETURNING tag_id
   )
INSERT INTO questiontags (question_id, tag_id)
SELECT q.question_id, t.tag_id
FROM   q, (
   SELECT tag_id FROM t
   UNION  ALL
   SELECT tag_id FROM input_tags JOIN tags USING (tag)
   ) t;
3 rows affected

TABLE questions;


question_id
title
body
userid
categoryid


1
some_title2
some_body2
3
5


TABLE tags;


tag_id
tag


1
bar

2
foo1

3
baz


TABLE questiontags;


question_id
tag_id


1
1

1
2

1
3