[postgis-users] Append a list of tables to an empty table to form a whole table

[Bo Victor Thomsen]

David, this is a list for Post*GIS *related questions. Your question is 
strictly Post*Gres* related.

However, you can use the following SQL commands:

-- Create new empty table with the same columns/types as "table_a".
CREATE TABLE new_table (LIKE table_a);

-- Create primary key constraint and index on geometry
ALTER TABLE new_table ADD PRIMARY KEY (your_pk_column); -- change to 
relevant column name
CREATE INDEX ON new_table USING GIST (your_geom_column); -- change to 
relevant column name

-- Insert rows from 3 tables with the same structure into table "new_table"
INSERT INTO new_table SELECT * FROM table_a;

-- Will only work if there is no overlap on primary key values in the 3 
tables
INSERT INTO new_table SELECT * FROM table_b;
INSERT INTO new_table SELECT * FROM table_c;

Best regards

Bo Victor Thomsen, Ms.Sc.Eng
aestasGIS Denmark
**

Den 14-10-2022 kl. 07:59 skrev Shaozhong SHI:
> How best to append a list of tables to an empty table to form a whole 
> table?
>
> Pseudo codes show the desirable logic.
>
>
>
> There are a list of tables with exactly same colums
> table_a
> table_b
> table_c
>
> Create an empty table  emp_table
>
> Foreach a_name in ARRAY ARRAY['table_a', 'table_b', 'table_c'] loop
>    insert into em_table as select * from table a_name
>
> end loop;
>
> Can something like that be done is PostGIS?
>
> Regards,
>
> David
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20221014/3dc7144c/attachment.htm>