pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
duplicate 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