I’ve been using Postgres’ foreign data wrappers for about a year now.
Stuff I’ve learned/done:
– Those of you on 9.5 can use the new IMPORT FOREIGN SCHEMA. If you’re not quite there yet, Leo and Regina’s function to script table creation as been saving me a ton of time.
I modified it slightly (gist here):
– I name my foreign servers after their source database, and organize the foreign tables into schemas named for the source db/foreign server, so I tweaked the function to handle that
– added a parameter to specify updateable true/false, since I explicitly set that for each table
– changed the output to my preferred formatting.
– Updating a server definition is much easier than I expected:
BEGIN; ALTER SERVER my_fdw_server OPTIONS ( SET dbname 'new_db_name' , SET host 'new_host_name' );
You can do the same thing with the table definitions; no need to drop & re-create them.
– Handling a serial field on the source table is interesting.
Say I have a table that looks like this:
CREATE TABLE table1 ( id serial primary key , name text , color text );
I create a foreign table on that, including all fields (exercise left to the reader). Then try to update it as I normally would, allowing Pg to handle the incrementing of the id field:
INSERT INTO table1 (name, color) VALUES ('bob', 'blue'); ERROR: null value in column "id" violates not-null constraint
I don’t want to pass in a value for id, because that negates the entire purpose of have a serial datatype.
My solution was to leave the id field out of the foreign table definition.
– Watch your datatypes, as CREATE FOREIGN TABLE doesn’t do any verification. “Fun” things can happen when you define your foreign table with timestamp without tz (to match the source table), then finally get approval to correct that on the source table, and don’t also change it in your foreign table definition.