Populate Temp Table Postgres

Question

I have the following three tables in the postgres db of my django app:

publication {
    id
    title
}

tag {
    id
    title
}

publication_tags{
    id
    publication_id
    tag_id
}

Where tag and publication have a many to many relationship.

I'd like to make a temp table with three columns: 1)publication title, 2)publication id, and 3)tags, where tags is a list (in the form of a string if possible) of all the tags on a given publication.

Thus far I have made the temp table and populated it with the publication id and publication title, but I don't know how to get the tags into it. This is what I have so far:

CREATE TEMP TABLE pubtags (pub_id INTEGER, pub_title VARCHAR(50), pub_tags VARCHAR(50))
INSERT INTO pubtags(pub_id, pub_title) SELECT id, title FROM apricot_app_publication

Can anyone advise me on how I would go about the last step?

mu is too short · Accepted Answer · 2013-06-11 21:57:04Z

1

Sounds like a job for string_agg:

string_agg(expression, delimiter)

input values concatenated into a string, separated by delimiter

So something like this should do the trick:

insert into pubtags (pub_id, pub_title, pub_tags)
select p.id, p.title, string_agg(t.title, ' ,')
from publication p
join publication_tags pt on (p.id = pt.publication_id)
join tag on (pt.tag_id = t.id)
group by p.id, p.title

You may want to adjust the delimiter, I guessed that a comma would make sense.

I'd recommend using TEXT instead of VARCHAR for your pub_tags so that you don't have to worry about the string aggregation overflowing the pub_tags length. Actually, I'd recommend using TEXT instead of VARCHAR period: PostgreSQL will treat them both the same except for wasting time on length checks with VARCHAR so VARCHAR is pointless unless you have a specific need for a limited length.

Also, if you don't specifically need pub_tags to be a string, you could use an array instead:

CREATE TEMP TABLE pubtags (
    pub_id INTEGER,
    pub_title TEXT,
    pub_tags TEXT[]
)

and array_agg instead of string_agg:

insert into pubtags (pub_id, pub_title, pub_tags)
select p.id, p.title, array_agg(t.title)
-- as above...

Using an array will make it a lot easier to unpack the tags if you need to.

answered Jun 11, 2013 at 21:57

mu is too short

436k71 gold badges863 silver badges822 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Erwin Brandstetter Over a year ago

I'd recommend using TEXT instead of VARCHAR period. I can subscribe to that. However, varchar without length specifier is effectively almost the same as text. No length checks.

mu is too short Over a year ago

@Erwin: True but my experience is that tools, ORMs, and assorted other bits of nastiness are happier to work with text than a length-less varchar so text tends to be easier in real life.

jac300 Over a year ago

Thanks again for your help. Follow up question: I decided to follow your array_agg suggestion. So now I'd like to select all the pub_titles for a pub_tag list that contains a particular string as one of its list items - for example, the list item 'tag'. I currently have, (which doesn't work) SELECT pub_title FROM pubtags FIND_IN_SET('%tag%', pub_tags). (This gives a syntax error). I've been searching for a solution, but this is the closest I've come. Any suggestions?

mu is too short Over a year ago

@user1697845: find_in_set is a MySQLism, no? I think the contains operator (@>) and ANY would be useful to you.

Collectives™ on Stack Overflow

Populate Temp Table Postgres

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related