Return to Answer

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

SELECT f1,f2,f3
FROM t1

UNION 

SELECT f1,f2,f3
FROM t2

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
UNION
SELECT * FROM region_2
UNION
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 essential 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.

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

SELECT f1,f2,f3
FROM t1

UNION 

SELECT f1,f2,f3
FROM t2

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
UNION
SELECT * FROM region_2
UNION
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.

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

SELECT f1,f2,f3
FROM t1

UNION 

SELECT f1,f2,f3
FROM t2

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
UNION
SELECT * FROM region_2
UNION
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.

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

SELECT f1,f2,f3
FROM t1

UNION 

SELECT f1,f2,f3
FROM t2

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
UNION
SELECT * FROM region_2
UNION
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 essential 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.

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

SELECT f1,f2,f3
FROM t1

UNION 

SELECT f1,f2,f3
FROM t2

Assuming the structure is exact across all three tables, the geometry field is called geom and fields are ordered the same way:

CREATE TABLE master AS 
SELECT * FROM region_1
UNION
SELECT * FROM region_2
UNION
SELECT * FROM region_3;

ALTER TABLE master ADD COLUMN gid serial4 PRIMARY KEY;
CREATE INDEX master_idx on 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.

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

SELECT f1,f2,f3
FROM t1

UNION 

SELECT f1,f2,f3
FROM t2

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
UNION
SELECT * FROM region_2
UNION
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.