0

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

  • Have you already tried using UNION (or UNION ALL)? – Andriy M May 27 '16 at 17:49
1

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;
  • How can I include more than just "old_table"? I have 6 tables that I need to include – Luffydude May 27 '16 at 14:55
  • Changed my answer. I kept my answer very generic to give you an idea on how to do it. – Marco May 27 '16 at 14:59
  • I got an error i.imgur.com/kfvAhvB.jpg – Luffydude May 27 '16 at 15:03
  • inserting into got me a lack of space error. guess I need to talk to the database guy for that but insert worked and union didnt – Luffydude May 27 '16 at 15:13
  • Checked the error that you got. I guess that the table ab6 has a different order of the fields or one of the field has a different type. Check the table definitions. If you had not run into space problems then you would have seen that the insert of ab6 would have failed too. – Marco May 27 '16 at 20:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.