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.
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?