Set a column value using the (remote) default when inserting a row into a foreign table
1
vote
1
answer
701
views
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 NULL
s 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?
Asked by user2233709
(223 rep)
Mar 30, 2023, 10:50 PM
Last activity: Mar 31, 2023, 12:39 PM
Last activity: Mar 31, 2023, 12:39 PM