Inserting into foreign table with sequences and default values

From: "peter(dot)borissow(at)kartographia(dot)com" <peter(dot)borissow(at)kartographia(dot)com>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: Inserting into foreign table with sequences and default values
Date: 2023-12-08 18:03:58
Message-ID: 1702058638.91064408@apps.rackspace.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general


Hello,
I have a few questions inserting data using Foreign Data Wrappers (FDW). Consider this simple example.

On PostgreSQL Database A (remote):

CREATE TABLE APP.TEST (
ID BIGSERIAL NOT NULL,
FIRST_NAME text,
LAST_NAME text,
STATUS integer NOT NULL DEFAULT 1,
CONSTRAINT PK_USER PRIMARY KEY (ID)
);

When I connect directly to this database I can insert without any issues. Example:

insert into app.test(first_name) values('Peter');
INSERT 0 1
-------

On PostgreSQL Database B (local):

CREATE SERVER remote_server FOREIGN DATA WRAPPER postgres_fdw ...
IMPORT FOREIGN SCHEMA APP LIMIT TO (TEST)
FROM SERVER remote_server INTO public;
-------

When I try to do a simple insert into the test table on database B I get an error:

insert into test(first_name) values('Mark');
ERROR: null value in column "id" of relation "test" violates not-null constraint

If I manually set the id, I get another error

insert into "user"(id, first_name) values(2, 'Mark');
ERROR: null value in column "status" of relation "test" violates not-null constraint

Only after I set both the id and status fields can I successfully insert.

insert into test(id, first_name, status) values(2, 'Mark', 1);
INSERT 0 1

I guess the FDW is not "seeing" the default value for status and the bigserial sequence for the id column. Is there anyway around this? Is there an option I missed when I called IMPORT FOREIGN SCHEMA? Or is there a hack using views or triggers?

Thanks in advance,
Peter



Responses

Browse pgsql-general by date

  From Date Subject
Next Message Vincent Veyron 2023-12-08 18:12:59 Re: running \copy through perl dbi ?
Previous Message David G. Johnston 2023-12-08 16:05:49 Re: running \copy through perl dbi ?