CODEWITHSUNDEEP
sqlpostgresql
Detuned
Detuned

Reputation: 3758

Splitting a column over a comma & inserting into a (many-to-many) table

I have a table of tags:

id  | text
----------
 1  | art
 2  | sports
 3  | history
 4  | science

and a table of posts (temporary):

id  | post             | tags
-----------------------------
 1  | 'random post 1'  | 'art, 'history'
 2  | 'random post 2'  | 'science'
 3  | 'random post 3'  | 'art, science, history'
 4  | 'random post 4'  | 'sports, science'

I'd like to build a many-to-many relationship between these tags and posts and I'm trying to figure out the sql query I'd need to build the following table (posts_tags)

id  | post_id | tag_id
-----------------------------
 1  | 1       | 1
 2  | 1       | 3
 3  | 2       | 4
 4  | 3       | 1
 ...

From the documentation, I can see that we can split the posts.tags table

regexp_split_to_table(posts.tags, E',') AS tags

but not sure how to make this work with the needed SQL command.

Upvotes: 0

Views: 61

Answers (2)

Patrick
Patrick

Reputation: 32336

First you need to create the table posts_tags:

CREATE TABLE posts_tags (
  id      serial  PRIMARY KEY,
  post_id integer REFERENCES posts,
  tag_id  integer REFERENCES tags
);

INSERT INTO posts_tags (post_id, tag_id)
  SELECT p.id, t.id
  FROM posts p, regexp_split_to_table(p.tags, ',') AS x(tag), tags t
  WHERE btrim(x.tag) = t."text";

The regexp_split_to_table() function uses a lateral join (sections 7.2.1.4 and .5) on table posts so that it can access the field tags. Effectively this works as a JOIN: you get a row for each combination of id with the tags in that same row. You then match the tags to the text field in the tags table such that you can SELECT the id of the tag.

Upvotes: 1

Lukasz Szozda
Lukasz Szozda

Reputation: 176074

You could first create posts_tags table and populate it based on both tables:

CREATE TABLE posts_tags(ID SERIAL PRIMARY KEY,
                        post_id INT NOT NULL,  -- define foreign keys
                        tag_id INT NOT NULL);

INSERT INTO posts_tags(post_id, tag_id)
SELECT c.id, t.id 
FROM (SELECT *, TRIM(regexp_split_to_table(p.tags, E',')) AS tag
      FROM posts p) AS c
JOIN tags t
  ON t."text" = c.tag

SELECT *
FROM posts_tags;

SqlFiddleDemo

Output:

╔═════╦══════════╦════════╗
║ id  ║ post_id  ║ tag_id ║
╠═════╬══════════╬════════╣
║  1  ║       1  ║      1 ║
║  2  ║       1  ║      3 ║
║  3  ║       2  ║      4 ║
║  4  ║       3  ║      1 ║
║  5  ║       3  ║      4 ║
║  6  ║       3  ║      3 ║
║  7  ║       4  ║      2 ║
║  8  ║       4  ║      4 ║
╚═════╩══════════╩════════╝

Upvotes: 0

Related Questions