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?.

-- OLD
CREATE TABLE blog_posts(
    id INTEGER PRIMARY KEY,
    blog_title TEXT,
    tags TEXT[]
);

INSERT INTO  blog_posts VALUES
 (1, 'foo', '{b,a,c}')
,(2, 'bar', '{b,c,d}')
,(3, 'baz', '{c,e,d,f}')
,(4, 'XXX', '{}')
,(5, 'YYY', NULL);


-- NEW
CREATE TABLE  blog(
   blog_id serial PRIMARY KEY
 , blog    text NOT NULL
);

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

CREATE TABLE blog_tag(
   blog_id int REFERENCES blog
 , tag_id  int REFERENCES tag
);
5 rows affected

WITH org AS (TABLE blog_posts)
, ins_blog AS (
   INSERT INTO blog (blog_id, blog)
   SELECT id, blog_title
   FROM   org
   )
, bt AS (
   SELECT unnest(tags) AS tag, id
   FROM   org
   )
, ins_tag AS (
   INSERT INTO tag (tag)
   SELECT DISTINCT tag
   FROM   bt
   ORDER  BY 1 -- optional
   RETURNING tag_id, tag
   )
INSERT INTO blog_tag (blog_id, tag_id)
SELECT id, tag_id
FROM   bt
JOIN   ins_tag USING (tag)
ORDER BY 1,2;  -- optional

SELECT setval(pg_get_serial_sequence('blog', 'blog_id'), max(blog_id)) FROM blog;  -- sync blog serial
10 rows affected



setval


5


TABLE tag;


tag_id
tag


1
a

2
b

3
c

4
d

5
e

6
f


TABLE blog;


blog_id
blog


1
foo

2
bar

3
baz

4
XXX

5
YYY


TABLE blog_tag;


blog_id
tag_id


1
1

1
2

1
3

2
2

2
3

2
4

3
3

3
4

3
5

3
6