2

I am trying to insert into a table in a Postgres database from two other Postgres databases using Foreign Data Wrappers. The objective is to have an autogenerate primary key, independent of the source, as there will be more than two in.

I first defined the tables like so:

Target database:

create table dummy (
  dummy_pk bigserial primary key
  -- other fields
);

Sources databases:

create foreign table dummy (
  dummy_pk bigserial
  -- other fields
) server ... ;

This solution worked fine as long as I inserted from only one source, when I tried to insert from the other one, without specifying dummy_pk, I got this message:

Duplicate key (dummy_pk)=(1)

Because postgres tries to insert an id of 1, I believe the sequence used for each source foreign table is different. I changed the source tables a bit in an attempt to let the target table's sequence do the job for the id:

create foreign table dummy (
  dummy_pk bigint
  -- other fields
) server ... ;

This time I got a diffrent error:

NULL value violates NOT NULL constaint on column « dummy_pk »

Therefore I believe the source server sends a query to the target where the dummy_pk is null, and the target does not replace it with the default value.

So, is there a way I can force the use of the target's sequence in a query executed on the source? Maybe I have to share that sequence, can I create a foreign sequence? I cannot remove the column on the foreign tables as I need a read access to them.

Thanks!

Community
  • 1
  • 1
TheWildHealer
  • 1,546
  • 1
  • 15
  • 26

1 Answers1

4

Remove dummy_pk from foreign tables so that destination table does not get NULL nor value and so fall backs to DEFAULT or NULL if no DEFAULT specified. If you attempt to pass DEFAULT to foreign table it will try to use DEFAULT value of foreign table instead.

create foreign table dummy (
  /*dummy_pk bigserial,*/
  column1 text,
  column2 int2,
  -- other fields
) server ... ;

Another way would be to grab sequence values from destination server using dblink, but I think this is better (if you can afford to have this column removed from foreign tables).

Łukasz Kamiński
  • 5,630
  • 1
  • 19
  • 32
  • Thanks for your andwer. I need that column in other queries. Can I create two foreign tables on referencing the same remote table in the same server? I could create one without the pk, one with? – TheWildHealer Jun 06 '19 at 09:33
  • Yes, just need to specify schema and/or table name in destination server if they are different (and they must if you have "dupliate" foreign table) `OPTIONS (schema_name 'some_schema', table_name 'some_name')` – Łukasz Kamiński Jun 06 '19 at 09:38
  • Yep, already did even though they have the same name, just to be sure, thanks. – TheWildHealer Jun 06 '19 at 09:43