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.

1 Answer 1


Use UNION or UNION ALL, depending on whether you want duplicates. Example:

SELECT f1,f2,f3


SELECT f1,f2,f3

Assuming the structure is exact across all three tables, the schema you're using is public, the geometry field is called geom and fields are ordered the same way:

CREATE TABLE public.master AS 
SELECT * FROM region_1
SELECT * FROM region_2
SELECT * FROM region_3;

ALTER TABLE public.master ADD COLUMN gid serial4 PRIMARY KEY;
CREATE INDEX master_idx on public.master using GIST(geom);

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 default serial8 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:

EXCEPT - select every value from the first query except those that appear in the second query.

INTERSECT - select every value from the first query that also appears in the second query.

  • 3
    Actually, that part about row_number() in views is awful advice, at least as far as ArcGIS goes. It can cause selection set and identify operations to fail horrifically. Best practice is to make the rowid column an actual column.
    – Vince
    Sep 7, 2021 at 22:48
  • 2
    As a FOSS guy, it's hard to keep track of ESRI's many bugs.
    – Encomium
    Sep 7, 2021 at 23:04
  • But thanks for that comment, @Vince. Will keep that in mind
    – Encomium
    Sep 7, 2021 at 23:22
  • I'm getting an error Syntax error at or near TABLE near the ALTER TABLE part.
    – gwydion93
    Sep 8, 2021 at 0:24
  • 1
    ALTER TABLE master ADD COLUMN gid serial4 PRIMARY KEY; <- is correct. Are you running both the table creation and the alter queries at once, and if so, do you have a semicolon separating them? I would run them separately if you get that syntax error. But there's nothing wrong with the ALTER TABLE syntax in the answer. Also, I'd add a schema before the table name just as good practice, i.e. public.master if the table is in the public schema.
    – Encomium
    Sep 8, 2021 at 1:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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