Re: duplicate key value violates unique constraint and duplicated records

Interesting!! We also met the same situation on PK running on PPAS 9.0 last
night.
When surfing Internet, got returned this URL :

https://www.postgresql.org/message-id/20140811083748.2536.10437%40wrigleys.
postgresql.org

<https://www.postgresql.org/message-id/20140811083748.2536.10437%40wrigleys.postgresql.org>
You can check the reply.

2017-06-29 17:28 GMT+08:00 Timokhin Maxim <ncx2(at)yandex(dot)com>:

> Hello.
> We are in process moving to new db from 9.4.8 -> 9.6.3.1. When we did it
> our application started to throw exception "duplicate key value violates
> unique constraint" during doing INSERT:
>
> INSERT INTO items (ctime, mtime, pubdate, url, title, description, body,
> status, fulltext_status, orig_id, image_id, video_id, resource_id,
> priority, checksum) VALUES (%(ctime)s, %(mtime)s, %(pubdate)s, %(url)s,
> %(title)s, %(description)s, %(body)s, %(status)s, %(fulltext_status)s,
> %(orig_id)s, %(image_id)s, %(video_id)s, %(resource_id)s, %(priority)s,
> %(checksum)s) RETURNING items.id'
>
> Column url has unique constraint.
>
>
> Also, we saw that during to update value into column status:
> (psycopg2.IntegrityError) duplicate key value violates unique constraint
> "items_url"
> DETAIL: Key (url)=(http://www.domainname.
> ru/ap_module/content/article/400-professional/140-professional/11880)
> already exists.
> [SQL: 'UPDATE items SET status=%(status)s WHERE items.id IN ( ...
> )...
>
>
> Our table:
>
>
> Column | Type |
> Modifiers
> -----------------+-----------------------------+------------
> -------------------------------------------------------
> id | integer | not null default
> nextval(('public.items_id_seq'::text)::regclass)
> ctime | timestamp without time zone | not null default now()
> pubdate | timestamp without time zone | not null default now()
> resource_id | integer | not null default 0
> url | text |
> title | text |
> description | text |
> body | text |
> status | smallint | not null default 0
> image | text |
> orig_id | integer | not null default 0
> mtime | timestamp without time zone | not null default now()
> checksum | text |
> video_url | text |
> audio_url | text |
> content_type | smallint | default 0
> author | text |
> video | text |
> fulltext_status | smallint | default 0
> summary | text |
> image_id | integer |
> video_id | integer |
> priority | smallint |
> Indexes:
> "items_pkey" PRIMARY KEY, btree (id)
> "items_url" UNIQUE, btree (url)
> "items_resource_id" btree (resource_id)
> "ndx__items__ctime" btree (ctime)
> "ndx__items__image" btree (image_id)
> "ndx__items__mtime" btree (mtime)
> "ndx__items__pubdate" btree (pubdate)
> "ndx__items__video" btree (video_id)
> Foreign-key constraints:
> "items_fkey1" FOREIGN KEY (image_id) REFERENCES images(id) ON UPDATE
> CASCADE ON DELETE SET NULL
> "items_fkey2" FOREIGN KEY (video_id) REFERENCES videos(id) ON UPDATE
> CASCADE ON DELETE SET NULL
> Referenced by:
> TABLE "cluster_image" CONSTRAINT "cluster_image_fkey2" FOREIGN KEY
> (item_id) REFERENCES items(id) ON UPDATE CASCADE ON DELETE CASCADE
> TABLE "cluster_meta" CONSTRAINT "cluster_meta_item_id_fkey" FOREIGN
> KEY (item_id) REFERENCES items(id) ON UPDATE CASCADE ON DELETE CASCADE
> TABLE "events" CONSTRAINT "events_fkey2" FOREIGN KEY (item_id)
> REFERENCES items(id) ON UPDATE CASCADE ON DELETE CASCADE
> TABLE "item_cluster" CONSTRAINT "item_cluster_fkey1" FOREIGN KEY
> (item_id) REFERENCES items(id) ON UPDATE CASCADE ON DELETE CASCADE
> TABLE "items_blogs" CONSTRAINT "items_blogs_fkey1" FOREIGN KEY
> (item_id) REFERENCES items(id) ON UPDATE CASCADE ON DELETE CASCADE
> TABLE "items_reflink" CONSTRAINT "items_reflink_fkey1" FOREIGN KEY
> (item_id) REFERENCES items(id) ON UPDATE CASCADE ON DELETE CASCADE
> TABLE "items_related" CONSTRAINT "items_related_fkey1" FOREIGN KEY
> (item_id) REFERENCES items(id) ON UPDATE CASCADE ON DELETE CASCADE
> TABLE "items_summaries" CONSTRAINT "items_summaries_fkey1" FOREIGN KEY
> (item_id) REFERENCES items(id) ON UPDATE CASCADE ON DELETE CASCADE
> TABLE "quotations" CONSTRAINT "quotations_fkey3" FOREIGN KEY (item_id)
> REFERENCES items(id) ON UPDATE CASCADE ON DELETE CASCADE
> TABLE "summaries" CONSTRAINT "summaries_fkey2" FOREIGN KEY (item_id)
> REFERENCES items(id) ON UPDATE CASCADE ON DELETE CASCADE
>
>
> Everything would be not bad if in the table weren't appeared duplicated
> records in url column.
> Any idea how is it possible?
>
> Thank you!
>
> --
> Timokhin 'maf' Maxim
>
>