PostgreSQL concatenate two tables on each row

Question

I have two tables, as such (where value in all cases is a character varying):

table_one:

|id|value|
|--+-----|
|1 |A    |
|2 |B    |
|3 |C    |
|4 |D    |

table_two:

|id|value|
|--+-----|
|11|0    |
|12|1    |
|13|2    |
|14|3    |
|15|4    |
|16|5    |

I want to do a query that results in the following:

result:

|value|
|-----|
|A_0  |
|A_1  |
|A_2  |
|A_3  |
|A_4  |
|A_5  |
|B_0  |
|B_1  |
|B_2  |
|B_3  |
|B_4  |
|B_5  |
|C_0  |
|C_1  |
|C_2  |
|C_3  |
|C_4  |
|C_5  |
|D_0  |
|D_1  |
|D_2  |
|D_3  |
|D_4  |
|D_5  |

This is for a seldom run report on PostgreSQL 8.4.9, so performance isn't critical. If the answer is 9.x specific, upgrading certainly isn't out of the question, so feel free to give 9.x specific answers. Any ideas?

Tomas Greif · Accepted Answer · 2013-09-16 13:03:17Z

1

select
 table_one.val || '_' || table_two.val
from
 table_one
 cross join table_two

Documentation for cross join.

SQLFiddle

edited Sep 16, 2013 at 13:03

answered Sep 16, 2013 at 11:29

Tomas Greif

22.9k25 gold badges114 silver badges162 bronze badges

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

5 Comments

Ihor Romanchenko Over a year ago

Better table_one cross join table_two.

Ihor Romanchenko Over a year ago

Also do not link to old versions of documentation. Use docs/current/static instead of docs/8.4/static.

Tomas Greif Over a year ago

@IgorRomanchenko Why? According to the documentation using , is equivalent to cross join (and also to table_one inner join table_two on (true)).

Tomas Greif Over a year ago

@IgorRomanchenko If the question is related to version 8.4 than I think it is appropriate to link to that version.

Ihor Romanchenko Over a year ago

First - table list (using where to join table) is a bad practice in general. Second - you will have problems if you wish to JOIN other tables on top of this two.

Collectives™ on Stack Overflow

PostgreSQL concatenate two tables on each row

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related