4

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
  ('AHMAD'),
  ('MAHMOOD');

Then I create the following foreign table in the second database

CREATE FOREIGN TABLE Foreign_Table (
  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)
0

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, privacy policy and cookie policy

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