1

I want to create a table (table_diff) that is a result of symmetrical difference (ST_SymDifference) between geometries (linestrings) from 2 tables (table1, table2) stored in 2 databases (db1, db2).

So far I have a command that works on 2 tables that are in the same database:

psql -U postgres -d db1 -c "CREATE TABLE table_diff AS SELECT ST_SymDifference(ST_Union(DISTINCT table1.geom),ST_Union(DISTINCT table2.geom)) AS geom FROM public.table1, public.table2

Below is my effort in trying to implement dblink to access table2 from db2 which doesn't work and which I need help with.

psql -U postgres -d db1 -c "CREATE TABLE table_diff AS SELECT ST_SymDifference(ST_Union(DISTINCT table1.geom),ST_Union(DISTINCT dblink('dbname=db2', 'SELECT geom FROM public.table2'))) AS geom FROM public.table1, dblink('dbname=db', 'SELECT geom FROM public.table2')"

The error I am getting:

ERROR:  a column definition list is required for functions returning "record"
1
  • Usually with dblink, you need an explict AS (col1, col2.....) after the query, which I suspect is what the error means. You might want to use postgres_fdw instead, as it is a lot less painful to deal with. I don't know how large your tables are, but neither dblink nor postgres_fdw will use indexes on a non-native Postgres type (ie, no spatial indexes), so this query is likely to be somewhat slow. – John Powell Oct 12 '18 at 12:25
1

The dblink function returns a dynamic SETOF RECORD, thus you will need to declare each return column in an alias list.

Updated:

You will also need to union both table's geometries separately to execute the final cross join, e.g. via CTE; assuming you are logged into db1, you can run

WITH
  t1 AS (
    SELECT ST_Union(geom) AS geom
    FROM public.table1
  ),

  t2 AS (
    SELECT ST_Union(geom) AS geom
    FROM dblink('<connstring_db2>', 'SELECT geom FROM table2') AS t(geom GEOMETRY)
  )

CREATE TABLE table_diff AS
    SELECT ST_SymDifference(t1.geom, t2.geom) AS geom
    FROM t1,
         t2;

You might want to consider using postgres_fdw as @JohnPowell suggested.

9
  • OP, actually has two dblink statements, not that this undermines your answer in any way. – John Powell Oct 12 '18 at 12:27
  • @JohnPowell hm yes, but I deemed that unnecessary...or actually wrong. but I might be wrong here!? – geozelot Oct 12 '18 at 12:28
  • No, I think you need two separate statements -- I could be wrong. I have always found dblink somewhat painful. I believe this would be much better handled with postgres_fdw syntax, which feels more natural, two CTE tables to fetch results, and then a final query to do the difference. – John Powell Oct 12 '18 at 12:32
  • @JohnPowell I will check...for now I updated the query with a generic dbname=<external_db> – geozelot Oct 12 '18 at 12:34
  • @JohnPowell so, yes and no; I didn´t really realize the ST_Union construct and changed my query accordingly now (also with CTE's for simplicity), but in general you wouldn´t need a second dblink if you only want to get one table from one foreign DB, since you should naturally be logged into the working DB already... – geozelot Oct 12 '18 at 13:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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