1

There is a later version where we are creating a foreign table and in the option field we right "table" instead of "table_name"?

In my work, we have an old oracle database with all alphanumeric information. To join alphanumeric information with geometric information the company creates back then foreign tables in PostgresSQl of some Oracle tables it works fine.

Now we are trying to create a plugin for qgis that will do some geometric analysis and insert alphanumeric data in the Oracle database using one foreign table of PostgresSQl. And there comes the problem. We cannot deal with the sequence id in the remote(Oracle) table.

In my remote machine, I solve the problem by creating the foreign table with all the columns except the id! So when I do the insertion I omit the id field and the sequence in the remote column works properly.

However in PostgreSQL in work, the foreign table has strange behavior, if I omit the id all columns sift left :´(! We try other approaches one of them we do something like Option(table (select field 1, field2)) but and I omit the id field in insertion, and an error occurred saying that I trying to write a null value in id field and its not allowed --'. I really think is because of the version of pwd extension or something.

There is an example of how the table is created: Work foreign table:

CREATE FOREIGN TABLE si.tbl_accaofiscal(
    idaccaofiscal numeric OPTIONS (key 'true') NOT NULL, (this is the problem field)
    identidadepredial numeric NOT NULL,
    idreparticaofinancas numeric NOT NULL,
    idtpaccaofiscal numeric NOT NULL,
    descricao character varying(255) NULL COLLATE pg_catalog."default",
)
    SERVER oracle_si
    OPTIONS (schema 'SI_As', table 'TBL_ACCAOFISCAL');

Remote foreing table:

CREATE FOREIGN TABLE si.tbl_accaofiscal(
    idaccaofiscal numeric OPTIONS (key 'true') NOT NULL, (i omit this value and everything works properly)
    identidadepredial numeric NOT NULL,
    idreparticaofinancas numeric NOT NULL,
    idtpaccaofiscal numeric NOT NULL,
    descricao character varying(255) NULL COLLATE pg_catalog."default",
)
    SERVER oracle_si
    OPTIONS (schema_name 'SI_As', table_name 'TBL_ACCAOFISCAL');

As you see the difference is in the Options field, in my localhost this field does not accept either "scheme" or "table".

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
Joao Folgado
  • 11
  • 1

1 Answers1

0

Different from postgres_fdw, oracle_fdw does not match columns by name, but by position. So the first column of the foreign table will be mapped to the first column of the Oracle table, regardless of the names.

So you cannot use that trick with oracle_fdw. One option would be to create a view on the Oracle side that doesn't have the primary key column, and to insert into that view.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • I try the solution with the query inside the Options field but I keep getting the same issue. I send the issue to your GitHub account with a better explanation. – Joao Folgado Oct 25 '22 at 15:26
  • That won't work. My suggestion is to run `CREATE VIEW` on the Oracle database. – Laurenz Albe Oct 25 '22 at 15:28