Oracle_pwd Insert problem

[Flgado]

Hi Laurenz, I see your answer to this issue https://github.com/laurenz/oracle_fdw/issues/228 However, I still Can´t insert a value in the remote oracle table by table omitting the id field. in the Postgres foreign table. I always have the same problem saying that I can't insert a null value on the primary key of the remote table. is like that Postgres foreign table even without having the id field.

The foreign table is this one:

The extension: The error:

Thanks very much, Regards

[laurenz]

If you write your code as formatted text rather than as images, I would have an easier time reproducing it.

What is the CREATE TABLE statement in Oracle? I just ran the following, and it worked like a charm.

On Oracle:

CREATE TABLE identity (
   id number(19) GENERATED ALWAYS AS IDENTITY
      CONSTRAINT identity_pkey PRIMARY KEY,
   value varchar2(100 CHAR)
      CONSTRAINT identity_value_null NOT NULL
) SEGMENT CREATION IMMEDIATE;

INSERT INTO identity (value) VALUES ('first');

COMMIT;

In PostgreSQL:

CREATE FOREIGN TABLE identity (
   value varchar(100) NOT NULL
) SERVER oracle
   OPTIONS (table '(SELECT value FROM identity)');

INSERT INTO identity VALUES ('second');

TABLE identity;

 value  
════════
 second
 first
(2 rows)

[laurenz]

Is there anything more I can do for you, or shall I close the ticket?

[Flgado]

Hi Laurenz,

Yes you can close the ticket. It's a limitation of the plugin.

I will have try the solution with one trigger in oracle.

Thanks very much, Best Regards

[laurenz]

Thanks for the feedback. To be exact, it is not a limitation of oracle_fdw, but a design choice of the foreign data wrapper API in general. All PostgreSQL foreign data wrappers have to behave like that.