By means of the PostgreSQL/PostGIS I want to replicate a function from a Python script into a SQL query. In my Python script, I have a list of feature classes and I use ArcPy's Merge_Management
to merge them together into a new layer called s_stud
:
study_lyr_list = ["region_1", "region_2", "region_3"]
arcpy.Merge_management(studies_lyr_list, s_studs)
I essentially have the same thing setup in PostGIS. I have tables for regions 1, 2, and 3 and I want to merge them together. They all share the same fields, so basically, I am just combining them together into a new master
table.
How would I do that as a query?
I have the PostGIS extensions.
Best Answer
Use UNION or UNION ALL, depending on whether you want duplicates. Example:
Assuming the structure is exact across all three tables, the schema you're using is
public
, the geometry field is calledgeom
and fields are ordered the same way:You may have to write out the field names in case fields aren't ordered in the same way, or if you want to skip some fields. Creating your primary key field using
serial4
rather than the defaultserial8
will avoid some issues ArcGIS has in reading PostgreSQL primary key fields. Both ArcGIS and QGIS require tables or views to have a field that passes for a valid unique identifier.Other useful query combination operators: