How to merge tables in PostgreSQL?

Asked by At

I want to merge two tables in the same database but not under the same schema of PostgreSQL (I use DBeaver to write scripts), but fail for some examples of merging tables in PostgreSQL.

The two table have lots of columns and samples, I want to select A B from table 1, and wang to select C D,E from table 2, where B and C items are exactly the same thing but numbers contained are not totally the same. Thus I want to merge and get A (B/C) D E.

I used UNION but it shows [42601]: ERROR: each UNION query must have the same number of columns. And when I used left join it shows mistake around '.'. In the last try my code like: select A from table1 left join table2.D, table2.E using B=C

1 Answers

0
zedfoxus On

You can use this kind of query:

Table

create table table1 (
    A text,
    B int
);
insert into table1 values ('test-a', 123);

create table table2 (
    C int,
    D text,
    E text
);
insert into table2 values (3456, 'test-d', 'test-e');

Query

select A::text, B::text as BC, '' as D, '' as E from table1
union all
select '' as A, C::text as BC, D::text, E::text from table2

Result

a       bc   d      e
test-a  123     
3456         test-d test-e

That'll take all records from table1 (columns A, B, dummy column D and dummy column E) and add to it records from table2 (dummy column A, column C, D and E)

Example: https://rextester.com/NWSEP53051

If you are using SQLite

Tables

create table table1 (A, B);
insert into table1 values ('test-a', 123);

create table table2 (C, D, E);
insert into table2 values (3456, 'test-d', 'test-e');

Query

select A, B as BC, '' as D, '' as E from table1
union all
select '' as A, C as BC, D, E from table2

Result

| A      | BC   | D      | E      |
| ------ | ---- | ------ | ------ |
| test-a | 123  |        |        |
|        | 3456 | test-d | test-e |

Example: https://www.db-fiddle.com/f/rE1MeJQpjGH4FZVwWmTpEX/0