CODEWITHSUNDEEP
postgresqlstored-proceduresconcatenation
Super_user_two
Super_user_two

Reputation: 81

Adding the results of two select queries into one table row with PostgreSQL

I am attempting to return the result of two distinct select statements into one row in PostgreSQL. For example, I have two queries each that return the same number of rows:

Select tableid1, tableid2, tableid3 from table1

+----------+----------+----------+
| tableid1 | tableid2 | tableid3 |
+----------+----------+----------+
|        1 |        2 |        3 |
|        4 |        5 |        6 |
+----------+----------+----------+

Select table2id1, table2id2, table2id3, table2id4 from table2

+-----------+-----------+-----------+-----------+
| table2id1 | table2id2 | table2id3 | table2id4 |
+-----------+-----------+-----------+-----------+
|         7 |         8 |         9 |        15 |
|        10 |        11 |        12 |        19 |
+-----------+-----------+-----------+-----------+

Now i want to concatenate these tables keeping the same number of rows. I do not want to join on any values. The desired result would look like the following:

+----------+----------+----------+-----------+-----------+-----------+-----------+
| tableid1 | tableid2 | tableid3 | table2id1 | table2id2 | table2id3 | table2id4 |
+----------+----------+----------+-----------+-----------+-----------+-----------+
|        1 |        2 |        3 |         7 |         8 |         9 |        15 |
|        4 |        5 |        6 |        10 |        11 |        12 |        19 |
+----------+----------+----------+-----------+-----------+-----------+-----------+

What can I do to the two above queries (select * from table1) and (select * from table2) to return the desired result above.

Thanks!

Upvotes: 1

Views: 2669

Answers (2)

Craig Ringer
Craig Ringer

Reputation: 324741

You can't have what you want, as you wrote the question. Your two SELECTs don't have any ORDER BY clause, so the database can return the rows in whatever order it feels like. If it currently matches up, it does so only by accident, and will stop matching up as soon as you UPDATE a row.

You need a key column. Then you need to join on the key column. Anything else is attempting to invent unreliable and unsafe joins without actually using a join.

Frankly, this seems like a pretty dodgy schema. Lots of numbered integer columns like this, and the desire to concatenate them, may be a sign you should be looking at using integer arrays, or using a side-table with a foreign key relationship, instead.

Sample data in case anyone else wants to play:

CREATE TABLE table1(tableid1 integer, tableid2 integer, tableid3 integer);
INSERT INTO table1 VALUES (1,2,3), (4,5,6);

CREATE TABLE table2(table2id1 integer, table2id2 integer, table2id3 integer, table2id4 integer);
INSERT INTO table2 VALUES (7,8,9,15), (10,11,12,19);

Depending on what you're actually doing you might really have wanted arrays.

I think you might need to read these two posts:

which explain that SQL tables just don't have an order. So you cannot fetch them in a particular order.

DO NOT USE THE FOLLOWING CODE, IT IS DANGEROUS AND ONLY INCLUDED AS A PROOF OF CONCEPT:

As it happens you can use a set-returning function hack to very inefficiently do what you want. It's incredibly ugly and *completely unsafe without an ORDER BY in the SELECTs, but I'll include it for completeness. I guess.

CREATE OR REPLACE FUNCTION t1() RETURNS SETOF table1 AS $$ SELECT * FROM table1 $$ LANGUAGE sql;
CREATE OR REPLACE FUNCTION t2() RETURNS SETOF table2 AS $$ SELECT * FROM table2 $$ LANGUAGE sql;
SELECT (t1()).*, (t2()).*;

If you use this in any real code then kittens will cry. It'll produce insane and bizarre results if the number of rows in the tables differ and it'll produce the rows in orderings that might seem right at first, but will randomly start coming out wrong later on.

THE SANE WAY is to add a primary key properly, then do a join.

Upvotes: 0

roman
roman

Reputation: 117485

You can use row_number() for join, but I'm not sure that you have guaranties that order of the rows will stay the same as in the tables. So it's better to add some order into over() clause.

with cte1 as (
   select
       tableid1, tableid2, tableid3, row_number() over() as rn
   from table1
), cte2 as (
   select
       table2id1, table2id2, table2id3, table2id4, row_number() over() as rn
   from table2
)
select *
from cte1 as c1
    inner join cte2 as c2 on c2.rn = c1.rn

Upvotes: 1

Related Questions