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