How to concatenate two tables in PostgreSQL?-CodePudding

I have two tables with different data like this:

CREATE TABLE table_01 (
  column_a VARCHAR,
  column_b VARCHAR
);

INSERT INTO table_01 
    (column_a, column_b) 
VALUES 
    ('data from table_01', 'data from table_01'),
    ('data from table_01', 'data from table_01');

CREATE TABLE table_02 (
  column_c VARCHAR,
  column_d VARCHAR
);

INSERT INTO table_02 
    (column_c, column_d) 
VALUES 
    ('data from table_02', 'data from table_02');

OUTPUT:

column_a                column_b
row_1 from table_01     row_1 from table_01
row_2 from table_01     row_2 data from table_01
_________________________________________________
column_c                column_d
row_1 from table_02     row_1 from table_02

OBS.: There's no relationship between these tables.

I'd like to merge them, as we can do in pandas, for instance, pd.concat(table_01, table_02, axis=1) aiming to merge the columns simply side by side, bringing me this result below, with no duplicate:

What is the simplest way to reach this goal in PostgreSQL? I have a fiddle sql sample here for you guys: https://dbfiddle.uk/?rdbms=postgres_13&fiddle=c10a4d80b5f59977369e577d0a484e70

CodePudding user response：

It is hard to join tables that are not related, but you could at least use the row_number with a left / right join to merge the columns:

WITH t1 AS (
  SELECT ROW_NUMBER() OVER (),*
  FROM table_01
)
SELECT column_a,column_b,column_c,column_d FROM t1
LEFT JOIN (SELECT ROW_NUMBER() OVER (),*
           FROM table_02) t2 ON t2.row_number = t1.row_number;

Demo: db<>fiddle

CodePudding user response：

You can use the ROW_NUMBER() function and perform a FULL JOIN using it. For example:

select
  a.column_a, a.column_b,
  b.column_c, b.column_d
from (
  select *, row_number() over() as rn from table_01
) a
full join (
  select *, row_number() over() as rn from table_02
) b on b.rn = a.rn

Result:

 column_a            column_b            column_c            column_d           
 ------------------- ------------------- ------------------- ------------------ 
 data from table_01  data from table_01  data from table_02  data from table_02 
 data from table_01  data from table_01  null                null

See running example at DB Fiddle.