Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rBfCo-00BoXU-WC for pgsql-general@arkaria.postgresql.org; Fri, 08 Dec 2023 18:04:07 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1rBfCn-00ADXe-Mj for pgsql-general@arkaria.postgresql.org; Fri, 08 Dec 2023 18:04:05 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rBfCn-00ADXW-4Y for pgsql-general@lists.postgresql.org; Fri, 08 Dec 2023 18:04:05 +0000 Received: from smtp115.iad3a.emailsrvr.com ([173.203.187.115]) by makus.postgresql.org with esmtps (TLS1.2) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rBfCi-009Rw7-Fw for pgsql-general@lists.postgresql.org; Fri, 08 Dec 2023 18:04:03 +0000 Received: from app46.wa-webapps.iad3a (relay-webapps.rsapps.net [172.27.255.140]) by smtp23.relay.iad3a.emailsrvr.com (SMTP Server) with ESMTP id 05D9C21A0B for ; Fri, 8 Dec 2023 13:03:59 -0500 (EST) Received: from kartographia.com (localhost.localdomain [127.0.0.1]) by app46.wa-webapps.iad3a (Postfix) with ESMTP id DF4DDC1C59 for ; Fri, 8 Dec 2023 13:03:58 -0500 (EST) Received: by apps.rackspace.com (Authenticated sender: peter.borissow@kartographia.com, from: peter.borissow@kartographia.com) with HTTP; Fri, 8 Dec 2023 13:03:58 -0500 (EST) X-Auth-ID: peter.borissow@kartographia.com Date: Fri, 8 Dec 2023 13:03:58 -0500 (EST) Subject: Inserting into foreign table with sequences and default values From: "peter.borissow@kartographia.com" To: pgsql-general@lists.postgresql.org MIME-Version: 1.0 Content-Type: multipart/alternative;boundary="----=_20231208130358000000_42694" Importance: Normal X-Priority: 3 (Normal) X-Type: html X-Client-IP: 170.250.244.158 Message-ID: <1702058638.91064408@apps.rackspace.com> X-Mailer: webmail/19.0.24-RC X-Classification-ID: 532a8aa1-7b77-4ccc-8182-47b1e4a422a7-1-1 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk ------=_20231208130358000000_42694 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable =0AHello,=0A I have a few questions inserting data using Foreign Data Wra= ppers (FDW). Consider this simple example.=0A =0AOn PostgreSQL Database A (= remote):=0A=0ACREATE TABLE APP.TEST (=0A ID BIGSERIAL NOT NULL,=0A FI= RST_NAME text,=0A LAST_NAME text,=0A STATUS integer NOT NULL DEFAULT = 1,=0A CONSTRAINT PK_USER PRIMARY KEY (ID)=0A);=0A =0AWhen I connect dire= ctly to this database I can insert without any issues. Example:=0A=0Ainsert= into app.test(first_name) values('Peter');=0AINSERT 0 1=0A-------=0A =0AOn= PostgreSQL Database B (local):=0A=0ACREATE SERVER remote_server FOREIGN DA= TA WRAPPER postgres_fdw ...=0AIMPORT FOREIGN SCHEMA APP LIMIT TO (TEST)=0A = FROM SERVER remote_server INTO public;=0A-------=0A =0AWhen I try to do = a simple insert into the test table on database B I get an error:=0A =0Ains= ert into test(first_name) values('Mark');=0AERROR: null value in column "i= d" of relation "test" violates not-null constraint=0A =0AIf I manually set = the id, I get another error=0A =0Ainsert into "user"(id, first_name) values= (2, 'Mark');=0AERROR: null value in column "status" of relation "test" vio= lates not-null constraint=0A =0AOnly after I set both the id and status fie= lds can I successfully insert.=0A =0Ainsert into test(id, first_name, statu= s) values(2, 'Mark', 1);=0AINSERT 0 1=0A =0AI 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 IMP= ORT FOREIGN SCHEMA? Or is there a hack using views or triggers?=0A =0AThank= s in advance,=0APeter=0A =0A =0A =0A ------=_20231208130358000000_42694 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable

Hello,

=0A

   I have a few questions inserting data using Forei= gn Data Wrappers (FDW). Consider this simple example.

=0A

 

=0A

On PostgreSQL Database A (remote):<= br />
CREATE TABLE APP.TEST (
    ID BIGSERIAL NOT= NULL,
    FIRST_NAME text,
    LAS= T_NAME text,
    STATUS integer NOT NULL DEFAULT 1,
    CONSTRAINT PK_USER PRIMARY KEY (ID)
);

=0A

 

=0A

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

=0A


insert into app.test(first_name) values('Peter');
I= NSERT 0 1

=0A

-------

=0A

&nb= sp;

=0A

On PostgreSQL Database B (local):

CREATE SERVER remote_server FOREIGN DATA WRAPPER postgres_fdw ...
IMP= ORT FOREIGN SCHEMA APP LIMIT TO (TEST)
    FROM SERVER = remote_server INTO public;

=0A

-------

=0A

 

=0A

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

=0A

 

=0A

insert into test(first_name) valu= es('Mark');

=0A

ERROR:  null value in column "i= d" of relation "test" violates not-null constraint

=0A

 

=0A

If I manually set the id, I get anothe= r error

=0A

 

=0A

insert= into "user"(id, first_name) values(2, 'Mark');

=0A

= ERROR:  null value in column "status" of relation "test" violates not-= null constraint

=0A

 

=0A

 

=0A

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

=0A

 

=0A

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 ca= lled IMPORT FOREIGN SCHEMA? Or is there a hack using views or triggers?

= =0A

 

=0A

Thanks in advance= ,

=0A

Peter

=0A

 

=0A=

 

=0A

 

=0A

 

------=_20231208130358000000_42694--