I have two local databases in one of them I have created a foreign table that is referencing a table in the second database. When I want to insert in foreign table it inserts the primary key all over from beginning from 1 although when I insert in the actual table it continues from the last value entered. Is there any way so if I insert in actual or foreign table it goes on the sequence of primary key?

CREATE TABLE Actual_Table (
  id   serial   PRIMARY KEY
  name varchar

Then I insert some data in the actual table

INSERT INTO Actual_Table (name) VALUES

Then I create the following foreign table in the second database

  id serial,
  name varchar
) server some_server options(schema_name 'public', table_name 'Actual_Table')

And then when I user insert in foreign table using the following insert statement I get an error that is violating the primary key constraint

INSERT INTO Foreign_Table (name) VALUES ('John');

This is the error I get

ERROR:  duplicate key value violates unique constraint "to_copy_pkey"
DETAIL:  Key (id)=(2) already exists.
CONTEXT:  Remote SQL command: INSERT INTO public.to_copy(id, name) VALUES ($1, $2)
  • 2
    Does it work if you create the foreign table with integer instead of serial?
    – user1822
    Mar 2, 2017 at 8:10
  • No, it still starts from 1 which is already in the column Mar 6, 2017 at 9:23
  • Then the sequence on the foreign table is out of sync with the underlying values in the table.
    – user1822
    Mar 6, 2017 at 9:32
  • exactly, how can we sync them? Mar 7, 2017 at 6:14
  • stackoverflow.com/q/244243/330315
    – user1822
    Mar 7, 2017 at 6:18

1 Answer 1


Can you ignore ID field in the Foreign_Table script and try inserting? It should use the current serial value in the Foreign Table.

CREATE FOREIGN TABLE Foreign_Table (name varchar) server 
    some_server options(schema_name 'public', table_name 'Actual_Table')

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.