How to Create a Table with Data from Other Tables in PostgreSQL

postgresql

How to create a table that with data from 6 other tables that have the same columns? The table names are: ab1_2, ab3, ab4, ab5 and ab6.

This is a spatial data with the Postgis extension enabled so they have geometry that I need to put in the first and second columns. The output table that I'm looking for is something like:

X coordinate | Y coordinate | field 3 | field 8 | field 20

Best Answer

From the PostgreSQL documentation:

CREATE TABLE new_table AS
SELECT * FROM ab1_2;

Replace the * with the field names (with alias if you need to change the name) you need. You can put in the other 5 tables as UNION:

CREATE TABLE new_table AS
SELECT * FROM ab1_2
UNION
SELECT * FROM ab3
UNION
SELECT * FROM ab4
UNION
SELECT * FROM ab5
UNION
SELECT * FROM ab6;

In the second one only the field names are needed. No use to give them an alias.

Or write 5 SQLs in the form:

INSERT INTO new_table
SELECT * FROM other_table;
Related Question