1

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?

1 Answer 1

2

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.

1
  • Thanks a lot for your help. I find your first option a little hackish, but I’m fine with the second option, that works fine. Mar 31, 2023 at 17:23

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.