pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedduplicate key value violates unique constraint "tableName_pk"
6+ messages / 6 participants
[nested] [flat]
* duplicate key value violates unique constraint "tableName_pk"
@ 2012-11-07 11:21 Saravanan Nagarajan <n.saravanan86@gmail.com>
0 siblings, 2 replies; 6+ messages in thread
From: Saravanan Nagarajan @ 2012-11-07 11:21 UTC (permalink / raw)
To: pgsql-bugs; +Cc: Saravanan Nagarajan <n.saravanan86@gmail.com>
Hi ,
I am using "Copy From" while dumping bulk data into PGSQL DB from file.
While I am trying to insert the rows, if the primary key already exists,
the complete transaction terminated.
Postgres cannot handle the constraint violations , i found it into
http://wiki.postgresql.org/wiki/COPY#Caveats_with_implementation .
Is there any possible way to insert bulk data using "Copy From" with
REPLACE/IGNORE duplicates.
Thanks & Regards
Saravanan.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: duplicate key value violates unique constraint "tableName_pk"
@ 2012-11-07 17:21 Pavan Deolasee <pavan.deolasee@gmail.com>
parent: Saravanan Nagarajan <n.saravanan86@gmail.com>
1 sibling, 0 replies; 6+ messages in thread
From: Pavan Deolasee @ 2012-11-07 17:21 UTC (permalink / raw)
To: Saravanan Nagarajan <n.saravanan86@gmail.com>; +Cc: pgsql-bugs
On Wed, Nov 7, 2012 at 4:51 PM, Saravanan Nagarajan <n.saravanan86@gmail.com
> wrote:
> Hi ,
>
> I am using "Copy From" while dumping bulk data into PGSQL DB from file.
> While I am trying to insert the rows, if the primary key already exists,
> the complete transaction terminated.
>
> Postgres cannot handle the constraint violations , i found it into
> http://wiki.postgresql.org/wiki/COPY#Caveats_with_implementation .
>
> Is there any possible way to insert bulk data using "Copy From" with
> REPLACE/IGNORE duplicates.
>
>
Did you look at http://pgbulkload.projects.pgfoundry.org/pg_bulkload.html ?
That advertises to have the features that you are looking for. I would
expect pg_bulkload to also perform equal if not better than COPY command.
Thanks,
Pavan
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: duplicate key value violates unique constraint "tableName_pk"
@ 2012-11-08 02:19 Craig Ringer <ringerc@ringerc.id.au>
parent: Saravanan Nagarajan <n.saravanan86@gmail.com>
1 sibling, 0 replies; 6+ messages in thread
From: Craig Ringer @ 2012-11-08 02:19 UTC (permalink / raw)
To: Saravanan Nagarajan <n.saravanan86@gmail.com>; +Cc: pgsql-bugs
On 11/07/2012 07:21 PM, Saravanan Nagarajan wrote:
> Hi ,
>
> I am using "Copy From" while dumping bulk data into PGSQL DB from
> file. While I am trying to insert the rows, if the primary key already
> exists, the complete transaction terminated.
>
> Postgres cannot handle the constraint violations , i found it
> into http://wiki.postgresql.org/wiki/COPY#Caveats_with_implementation .
>
> Is there any possible way to insert bulk data using "Copy From" with
> REPLACE/IGNORE duplicates.
Typically you COPY into a temporary or UNLOGGED table, then merge the
data into the target table using appropriate `UPDATE ... FROM ... WHERE`
and `INSERT ... SELECT ... WHERE` statements.
--
Craig Ringer
^ permalink raw reply [nested|flat] 6+ messages in thread
* Duplicate key value violates unique constraint
@ 2020-03-16 15:55 Nafees Sardar <nafees@epazz.net>
0 siblings, 2 replies; 6+ messages in thread
From: Nafees Sardar @ 2020-03-16 15:55 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org
Hi support,
I migrate my data from MS Sql server to postgreSQL database, now when I try
to insert new row it gives me error "duplicate key value violates unique
constraint". For example I have a table which contains 100 rows of records
when I go for inserting 101 row it give duplicate value constraint error.
When I pass PK value manually its working, and one more thing auto increment
is enabled for all tables .
Can you please let me know anything I am missing? I will really appreciate
it.
Thanks
Regards
Nafees Sardar
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Duplicate key value violates unique constraint
@ 2020-03-16 16:23 David G. Johnston <david.g.johnston@gmail.com>
parent: Nafees Sardar <nafees@epazz.net>
1 sibling, 0 replies; 6+ messages in thread
From: David G. Johnston @ 2020-03-16 16:23 UTC (permalink / raw)
To: Nafees Sardar <nafees@epazz.net>; +Cc: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
On Monday, March 16, 2020, Nafees Sardar <nafees@epazz.net> wrote:
> Hi support,
>
>
>
> I migrate my data from MS Sql server to postgreSQL database, now when I
> try to insert new row it gives me error “duplicate key value violates
> unique constraint”. For example I have a table which contains 100 rows of
> records when I go for inserting 101 row it give duplicate value constraint
> error. When I pass PK value manually its working, and one more thing auto
> increment is enabled for all tables .
>
>
>
> Can you please let me know anything I am missing? I will really
> appreciate it.
>
ALTER SEQUENCE
David J.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Duplicate key value violates unique constraint
@ 2020-03-16 16:27 Tom Lane <tgl@sss.pgh.pa.us>
parent: Nafees Sardar <nafees@epazz.net>
1 sibling, 0 replies; 6+ messages in thread
From: Tom Lane @ 2020-03-16 16:27 UTC (permalink / raw)
To: Nafees Sardar <nafees@epazz.net>; +Cc: pgsql-bugs@lists.postgresql.org
"Nafees Sardar" <nafees@epazz.net> writes:
> I migrate my data from MS Sql server to postgreSQL database, now when I try
> to insert new row it gives me error "duplicate key value violates unique
> constraint". For example I have a table which contains 100 rows of records
> when I go for inserting 101 row it give duplicate value constraint error.
> When I pass PK value manually its working, and one more thing auto increment
> is enabled for all tables .
Sounds like you need to adjust the sequence counters underlying the
auto-increment columns so that their settings are higher than any
of the already-existing ID values. Auto-increment isn't really a
native concept in Postgres, it's something you have to build from
a sequence object and a suitable default expression. Getting the
sequence set correctly is an easy step to miss when migrating from
a different DBMS.
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2020-03-16 16:27 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-11-07 11:21 duplicate key value violates unique constraint "tableName_pk" Saravanan Nagarajan <n.saravanan86@gmail.com>
2012-11-07 17:21 ` Pavan Deolasee <pavan.deolasee@gmail.com>
2012-11-08 02:19 ` Craig Ringer <ringerc@ringerc.id.au>
2020-03-16 15:55 Duplicate key value violates unique constraint Nafees Sardar <nafees@epazz.net>
2020-03-16 16:23 ` Re: Duplicate key value violates unique constraint David G. Johnston <david.g.johnston@gmail.com>
2020-03-16 16:27 ` Re: Duplicate key value violates unique constraint Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox