pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: peter.borissow@kartographia.com <peter.borissow@kartographia.com>
To: pgsql-general@lists.postgresql.org
Subject: Inserting into foreign table with sequences and default values
Date: Fri, 8 Dec 2023 13:03:58 -0500 (EST)
Message-ID: <1702058638.91064408@apps.rackspace.com> (raw)


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
 
 
 
 

view thread (4+ messages)  latest in thread

Message-ID: <1702058638.91064408@apps.rackspace.com>
Permalink:  ../1702058638.91064408@apps.rackspace.com/
Also on:    postgresql.org/message-id/1702058638.91064408@apps.rackspace.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-general@postgresql.org
  Cc: peter.borissow@kartographia.com
  Subject: Re: Inserting into foreign table with sequences and default values
  In-Reply-To: <1702058638.91064408@apps.rackspace.com>
  References: <1702058638.91064408@apps.rackspace.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox