patternsqlMinor
Inserting in PostgreSQL Foreign Table Violates Primary Key Constraint10
Viewed 0 times
postgresqlconstraint10primaryviolatesforeigninsertingtablekey
Problem
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?
Then I insert some data in the actual table
Then I create the following foreign table in the second database
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
This is the error I get
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)Solution
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')
}Code Snippets
{
CREATE FOREIGN TABLE Foreign_Table (name varchar) server
some_server options(schema_name 'public', table_name 'Actual_Table')
}Context
StackExchange Database Administrators Q#165921, answer score: 2
Revisions (0)
No revisions yet.