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