My understanding is that you can query across servers if you use postgres_fdw. I have set this connection up successfully following the official documentation. However, I am struggling to construct the query correctly so it recognises my database on the other server.

Lets say:

foreign_database sits on the foreign_server and contains a table called table_1

My local database contains a table called table_2

I am trying to create a view of data from table_1 where it intersects table_2. I am using the below:

CREATE VIEW my_test_viewname AS

SELECT * FROM foreign_database.table_1

JOIN table_2 on ST_Intersects(table_2.geom, table_1.geom)

I am returned with the error:

ERROR: relation "foreign_database.table_1" does not exist LINE 2: SELECT * FROM foreign_database.table_1 ^ SQL state: 42P01 Character: 45

How do I correctly reference this table on the other server?

  • what does \det show in the psql terminal. – John Powell Feb 26 at 9:44
  • John, interestingly it suggests there are no tables. I followed the docs up until Create User Mapping. Is it that I need to Create Foreign Table as well? Is this the bit I've missed to complete the link? – JClarkson Feb 26 at 10:48
  • Yes, you have to actually create the tables with a similar definition to the source. – John Powell Feb 26 at 10:56

Your Answer

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

Browse other questions tagged or ask your own question.