CODEWITHSUNDEEP
sqlpostgresqlselecttemp-tables
jac300
jac300

Reputation: 5222

Populate Temp Table Postgres

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?

Upvotes: 0

Views: 1082

Answers (1)

mu is too short
mu is too short

Reputation: 434585

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.

Upvotes: 1

Related Questions