Postgres: Convert single rows from multiple tables into multiple rows in a single table

Question

I have data scattered across multiple tables and rows that I'd like to aggregate into a more usable format for my use case. The problem boils down to something like this...

If I have two tables like this:

product_id  title_id  description_id
1           123       234 
2           345       456
3           567       678

product_id  additional_info_id
1           789
1           890
2           901

How would I construct a query to return data like this?

product_id  content_id  content_type
1           123         title 
1           234         description
1           789         additional_info
1           890         additional_info
2           345         title
2           456         description
2           901         additional_info
3           567         title
3           678         Description

I found this post and I can construct a query like this

select
    p.product_id,
    p_content.*
from
    product p,
    lateral (values
        (p.title_id, 'title'),
        (p.description_id, 'description')
    ) p_content (content_id, content_type)
;

to get the data from the product table in the format I need, but I can't figure out how to incorporate the data from the additional_info table.

score 4 · Accepted Answer · answered Sep 25 '19 at 14:52

You can use union all:

select p.product_id, v.content_id, v.content_type
from product p cross join lateral
     (values (p.title_id, 'title'),
             (p.description_id, 'description')
     ) v(content_id, content_type)
union all
select product_id, additional_info_id, 'additional_info'
from additional_info ai;

Postgres: Convert single rows from multiple tables into multiple rows in a single table

1 Answers1