Set a column value using the (remote) default when inserting a row into a foreign table

Question

I have a database with a table whose primary key is a serial column, or a column with a locally-computed default value that prevents conflicts, for instance:

CREATE TABLE foo (
    foo_id serial PRIMARY KEY,
    foo_name text
);

I also have a second database, where I use the postgres_fdw foreign data wrapper to access the table in the first database.

I’d like to insert a row in the foreign table in the second database, without specifying a value for the primary key, letting the remote server “choose” a value in a conflict-free way.

Unfortunately, whenever I try to insert data in the foreign table, without selecting the primary key column, postgres_fdw tries to insert rows with NULLs for the columns that weren’t selected, without using the server-defined default values. Hence it fails as the primary key is defined NOT NULL.

As far as I can see, there is not way to use a foreign sequence. I’ve seen that I can define a default value when I create a foreign table but, as I understand it, it is implemented on the local side.

Is there a way I can insert a row in a foreign table and let the foreign server use its default values?

Answer 1

Yes, PostgreSQL always uses the local default value, and there is no way to change that. I can think of two remedies:

1. Define a special default value like -1 for the id column of the foreign table, and on the remote table define a BEFORE INSERT trigger WHEN (NEW.id = -1) that replaces the special value with the next sequence value. Using a trigger with a WHEN clause has the advantage that the trigger function is only called when necessary.

2. Define a foreign table that does not include the id column. If it is too awkward to INSERT into one foreign table and SELECT from another one, define a view on the latter that has an INSTEAD OF INSERT trigger that inserts into the former table instead.