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.
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