Dynamic UNION ALL query in Postgres

Question

We are using a Postgres / PostGis connection to get data that is published via a geoserver.

The Query looks like this at the moment:

SELECT 
    row_number() over (ORDER BY a.ogc_fid) AS qid, a.wkb_geometry AS geometry
FROM
(
   SELECT * FROM test
   UNION ALL
   SELECT * FROM test1
   UNION ALL
   SELECT * FROM test2
)a

In our db only valid shapefiles will be imported each in a single table so it would make sense to make the UNION ALL part dynamic (loop over each table and make the UNION ALL statement). Is there a way to do this in a standard Postgres way or do I need to write a function and how would the syntax look like? I am pretty new to SQL.

The shapefiles have a different data structure and only the ogc_fid column and the wkb_geometry column are always available and we would like to union all tables from the DB.

I don't really know what this shapefile thing is, but why don't you just import everything into a single table? Another option would be to use table inheritance — user330315
– user330315, Commented Feb 18, 2016 at 15:55
Your question isnt really clear. So the answer depend on what you want to do. But the easy solve is create a DYNAMIC QUERY but you need a way to know what are the tables you want to join — Juan Carlos Oropeza
– Juan Carlos Oropeza, Commented Feb 18, 2016 at 15:58
in shapefiles you can store geodata. We woud like to avoid storing everything in a single table because the data structure of the geodata can be different and we would like to keep all of the columns of each files for other queries — Bernd Loigge
– Bernd Loigge, Commented Feb 18, 2016 at 16:00
@JuanCarlosOropeza - I updated my question. In this particular DB all tables need to be Unioned — Bernd Loigge
– Bernd Loigge, Commented Feb 18, 2016 at 16:04
If you want to have different columns in each table, then you can't possibly use them in a union statement. — user330315
– user330315, Commented Feb 18, 2016 at 17:35

Juan Carlos Oropeza · Accepted Answer · 2016-02-20 02:59:37Z

3

This is just general guidelines you need work in the details specially syntaxis.

You need create a store procedure

Create a loop checking information_schema.tables filter for the tablenames you want

DECLARE    
    rec record;
    strSQL text;
BEGIN

Then create a strSQL with each table

 FOR rec IN SELECT table_schema, table_name
            FROM information_schema.tables                
 LOOP
     strSQL := strSQL || 'SELECT ogc_fid, wkb_geometry FROM ' || 
               rec.table_schema || '.' || rec.table_name || ' UNION ';
 END LOOP;

-- have to remove the last ' UNION ' from strSQL    

strSQL := 'SELECT  row_number() over (ORDER BY a.ogc_fid) AS qid,
         a.wkb_geometry AS geometry FROM (' || strSQL || ')';

EXECUTE strSQL;

edited Feb 20, 2016 at 2:59

answered Feb 18, 2016 at 16:30

Juan Carlos Oropeza

48.4k14 gold badges87 silver badges128 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Juan Carlos Oropeza Over a year ago

@ClodoaldoNeto What make you think this is SQL Server?

Bernd Loigge Over a year ago

thank you for your answer - that helped a lot. I ended up using UNION ALL. btw. I think there is a small mistake: it should be "rec.table_name || " instead of "rec.table_name + "

Juan Carlos Oropeza Over a year ago

You are right, sometimes I mix sql server and postgres syntaxis.

lookslikeanevo Over a year ago

@JuanCarlosOropeza I am trying to use your solution and I am getting a syntax error at or near record.. any ideas?

Timothy Dalton Over a year ago

Removing the last UNION from the string could work this way strSQL := left(strSQL, -6);

Geir Bostad · Accepted Answer · 2016-02-18 20:41:33Z

One solution is to serialize the rest of the columns to json with row_to_json(). (available since PostgreSQL9.2). For PG9.1 (and earlier) you can use hstore, but note that all values are cast to text.

Why serialize? It is not possible to union rows where the number of colums vary, or the datatypes do not match between the union queries.

I created a quick example to illustrate:

--DROP SCHEMA testschema CASCADE;
CREATE SCHEMA testschema;

CREATE TABLE testschema.test1 (
    id integer,
    fid integer,
    metadata text
);

CREATE TABLE testschema.test2 (
    id integer,
    fid integer,
    city text,
    count integer
);

CREATE TABLE testschema.test3 (
    id integer,
    fid integer
);


INSERT INTO testschema.test1 VALUES (1,   4450, 'lala');
INSERT INTO testschema.test2 VALUES (33,  6682, 'London', 12345);
INSERT INTO testschema.test3 VALUES (185, 8991);


SELECT 
    row_number() OVER (ORDER BY a.fid) AS qid, a.*
FROM
(
    SELECT id, fid, row_to_json(t.*) AS jsondoc FROM testschema.test1 t
    UNION ALL 
    SELECT id, fid, row_to_json(t.*) AS jsondoc FROM testschema.test2 t
    UNION ALL 
    SELECT id, fid, row_to_json(t.*) AS jsondoc FROM testschema.test3 t    
) a

SELECT output:

qid  id    fid    jsondoc
1;   1;    4450;  "{"id":1,"fid":4450,"metadata":"lala"}"
2;   33;   6682;  "{"id":33,"fid":6682,"city":"London","count":12345}"
3;   185;  8991;  "{"id":185,"fid":8991}"

Collectives™ on Stack Overflow

Dynamic UNION ALL query in Postgres

2 Answers 2

5 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related