2

I have a localhost Postgres 9.3 server running with multiple database. One of those is Travelingo_data. It has the extensions postgres_fdw and postgis installed.

What I would like to do is to run a series of queries from another database on the same server and combine these with inserts in Travelingo_data.

I think the postgres_fdw extension can be used for exactly this goal Therefore, I executed the following code in PSQL Console from de Travelingo_data prompt

CREATE SERVER app_db
FOREIGN DATA WRAPPER postgres_fdw 
OPTIONS (dbname 'top10nl', port '5432', host 'localhost');

The top10nl database contains data that I what to select and insert into a table in Travelingo_data

CREATE USER MAPPING for postgres
SERVER app_db
OPTIONS (user 'postgres', password 'postgres');

Its the same server and always the same user

CREATE FOREIGN TABLE fg_punt
(
  ogc_fid serial NOT NULL,
  wkb_geometry geometry(Point,28992),
  gml_id character varying,
  fid integer,
  identificatie character varying,
  objectbegintijd character varying,
  versiebegintijd character varying,
  status character varying,
  naamnl character varying,
  naamfries character varying,
  typefunctioneelgebied character varying,
  brontype character varying,
  bronbeschrijving character varying,
  bronactualiteit character varying,
  bronnauwkeurigheid double precision,
  dimensie character varying,
  visualisatiecode integer,
  tdncode integer

)
 SERVER app_db OPTIONS (schema_name 'ttnl',      table_name 'functioneelgebied_punt')

The defined columns in the foreign table are exactly the same as the original existing table in database top10nl

First of all can anybody detect any wrong paramaters? Secondly: I can't see any references in PGAdmin of the foreign table, or server. Where should I see the foreign table listed? I expected to see a foreign table named fg_punt in the tables list of Travelingo_data (of which I could select data), but there is none??

4
  • So what happens when you run a select * from fg_punt;? Commented Nov 21, 2015 at 10:47
  • Why do you assume any wrong parameters? Is there any error message, you get? When do you get it and what does it say? Commented Nov 22, 2015 at 8:53
  • In the SQL window of PGAdmin (after selecting Travelingo_data database the error is: ERROR: relation "fg_punt" does not exist Commented Nov 22, 2015 at 12:01
  • In the PSQL Console the same Commented Nov 22, 2015 at 12:02

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.