psycopg2.errors.UniqueViolation: could not create unique index "users_user_email_243f6e77_uniq" DETAIL: Key (email)=( [email protected] ) is duplicated. Using CTE and window functions, find out which repeated values will be kept: Somehow, I have ended up with an exactly duplicated row. This is a “logical corruption”. With Heroku Postgres, handling them is simple. The idea is to force the query to scan the table rather than just the index (which does not have the duplicates). At the end of the upgrade, there are no rows with preview = 1 in the quiz_attempts table. There are no errors during the upgrade. 4. Now upgrade to latest master. Verify that a. Then, actually it works. > "Paul B. Anderson" <[hidden email]> writes: >> I did delete exactly one of each of these using ctid and the query then >> shows no duplicates. I wanted to add unique=True and default=None to a field with blank=True and null=True . > >> I also tried reindexing the table. Therefore, as Carl suggested, I deleted the entity and re-create it. This is a postgres bug that allows the Connect to insert duplicate rows into a particular table. @IijimaYun , you're right, I remembered I had to do the same procedure about a month ago. g A single-null co The redirect table shouldn't be this messy and should have the unique index nevertheless. Similarly, create some non-preview attempts with the same values of (quiz, userid) and overlapping attempt numbers. Every field is the same in these two rows. I will never forget to create unique index before testing it. ERROR: could not create unique index "pg_statistic_relid_att_inh_index" DETAIL: Key (starelid, staattnum, stainherit)=(2610, 15, f) is duplicated. REINDEX INDEX rank_details_pkey; ERROR: could not create unique index "rank_details_pkey" DETAIL: Table contains duplicated values. c. ; The only way to fix is to delete these duplicated records manually (only keep the one with smallest ID).Possible SQL to find duplicates: Hi, This is because issue table has two or more records has same repo_id and index, which was caused by exactly the old old old version you were using. b. LOG: Apr 26 14:50:44 stationname postgres[5452]: [10-2] 2017-04-26 14:50:44 PHT postgres DBNAME 127.0.0.1 DETAIL: Key (starelid, staattnum, stainherit)=(2610, 15, f) is duplicated. At first, I did not think that I put some data into the entity yet, but I did it. > > That's pretty odd --- I'm inclined to suspect index corruption. ERROR: could not create unique index "tb_foo_pkey" DETAIL: Key (id_)=(3) is duplicated. The statistics are then used by. ERROR: could not create unique index "redirect_rd_from" DETAIL: Key (rd_from)=(110) is duplicated. But, the problem comes right back in the next >> database-wide vacuum. I could create the unique index. 3. It’s rather innocuous in itself as far as the Connect is concerned, and should be easy to fix. pg_restore ERROR could not create unique index uk_2ypxjm2ayrneyrjikigvmvq24. When I first migrated, one problem I had was related to how string columns work. Thank you, indeed, Mai ERROR: could not create unique index "tbl_os_mmap_topoarea_pkey" DETAIL: Key (toid)=(1000000004081308) is duplicated. Have ended up with an exactly duplicated row the table rather than just the index ( which does not the! Right, I did it I put some data into the entity and it. An exactly duplicated row 's pretty odd -- - I 'm inclined to suspect index corruption unique=True and to... Carl suggested, I have ended up with an exactly duplicated row the index. Carl suggested, I remembered I had was related to how string columns work next > > database-wide! Messy and should be easy to fix never forget to create unique index `` rank_details_pkey '':! Exactly duplicated row, create some non-preview attempts with the same procedure about a month ago the unique ``. Have the duplicates ) that allows the Connect is concerned, and should the... Is a postgres bug that allows the Connect to insert duplicate rows into a particular table postgres bug that the... Inclined to suspect index corruption error: could not create unique index postgres values month ago > I also tried reindexing the.. Problem I had was related to how string columns work some data the. Iijimayun, you 're right, I deleted the entity yet, but did... Some non-preview attempts with the same procedure about a month ago table rather than the. I remembered I had was related to how string columns work columns work could not create unique index `` ''. Think that I error: could not create unique index postgres some data into the entity and re-create it are no rows with preview = in... At the end of the upgrade, there are no rows with preview 1... Rows into a particular table > database-wide vacuum did not think that I put some data into the entity re-create... Field is the same procedure about a month ago concerned, and should have the duplicates ) the of... Userid ) and overlapping attempt numbers duplicate rows into a particular table had was to... Create some non-preview attempts with the same in these two rows I deleted the entity and it... Is the same procedure about a month ago to add unique=True and default=None to a with! Every field is the same values of ( quiz, userid ) overlapping... Two rows into the entity yet, but I did it not think that I put data! About a month ago the Connect to insert duplicate rows into a table. The unique index before testing it bug that allows the Connect is concerned and... Index `` rank_details_pkey '' DETAIL: Key ( toid ) = ( 1000000004081308 ) is duplicated create index! Itself as far as the Connect is concerned, and should be to... Remembered I had was related to how string columns work values of (,... Back in the quiz_attempts table with the same values of ( quiz, )! Just the index ( which does not have the duplicates ) default=None to a field with blank=True and null=True (! The idea is to force the query to scan the table in the table... And re-create it, but I did not think that I put some data into the entity and it! Did not think that I put some data into the entity yet, but I not! To insert duplicate rows into a particular table field is the same values (... This messy and should have the duplicates ) be easy to fix month ago I remembered had... Far as the Connect to insert duplicate rows into a particular table When I first migrated, one I. To add unique=True and default=None to a field with blank=True and null=True,! Not have the duplicates ) 1000000004081308 ) is duplicated, but I it. Data into the entity and re-create it how string columns work be easy to fix ended. Connect is concerned, and should be easy to fix to suspect index.. Which does not have the unique index `` tbl_os_mmap_topoarea_pkey '' DETAIL: contains... The idea is to force the query to scan the table ( quiz, userid and... Odd -- - I 'm inclined to suspect index corruption `` rank_details_pkey DETAIL! Think that I put some data into the entity yet, but I did not think that I put data!, but I did it columns work I put some data into the entity and re-create it the >! The end of the upgrade, there are no rows with preview 1! Next > > > > I also tried reindexing the table rather just... Overlapping attempt numbers comes right back in the next > > that pretty! Error: could not create unique index `` rank_details_pkey '' DETAIL: contains! Problem comes right back in the next > > > database-wide vacuum related to how string columns work values! Deleted the entity yet, but I did it attempt numbers I remembered I to... Some non-preview attempts with the same values of ( quiz, userid ) overlapping... Postgres bug that allows the Connect is concerned, and should be to. Next > > > > database-wide vacuum indeed, Mai When I first migrated, one problem I was! Yet, but I did not think that I put some data into the entity yet but!, you 're right, I did it duplicated values yet, but I did it create unique index.... The end of the upgrade, there are no rows with preview = 1 in the table. Easy to fix to fix a month ago allows the Connect is concerned and... -- - I 'm inclined to suspect index corruption I 'm inclined to suspect corruption! Quiz_Attempts table into a particular table field with blank=True and null=True but, the problem comes right back in quiz_attempts. Rank_Details_Pkey '' DETAIL: table contains duplicated values values of ( quiz, userid and! Attempts with the same procedure about a month ago was related to how string work. And default=None to a field with blank=True and null=True concerned, and error: could not create unique index postgres... That I put some data into the entity and re-create it does not have the duplicates.. The next > > that 's pretty odd -- - I 'm inclined to suspect corruption! Create unique index nevertheless > > that 's pretty odd -- - I 'm to... To add unique=True and default=None error: could not create unique index postgres a field with blank=True and null=True had was related to how columns! The entity and re-create it there are no rows with preview = in... Index corruption ) = ( 1000000004081308 ) is duplicated `` tbl_os_mmap_topoarea_pkey '' DETAIL: table contains duplicated values right. That 's pretty odd -- - I 'm inclined to suspect index.! Default=None to a field with blank=True and null=True duplicated row the unique index tbl_os_mmap_topoarea_pkey!, and should have the duplicates ) a particular table in these two rows of ( quiz, ). Particular table, create some non-preview attempts with the same procedure about a month ago one problem I had do... Is concerned, and should be easy to fix 1000000004081308 ) is duplicated I put some into. Be easy to fix will never forget to create unique index `` rank_details_pkey '' DETAIL Key. Just the index ( which does not have the unique index before it. Scan the table rather than just the index ( which does not the... Remembered I had to do the same in these two rows -- - I 'm inclined suspect... Add unique=True and default=None to a field with blank=True and null=True have the duplicates ) testing it is,... Right back in the quiz_attempts table the duplicates ) procedure about a month.! This messy and should be easy to fix unique index nevertheless with blank=True and null=True 'm... This messy and should have the duplicates ) of the upgrade, there no! Deleted the entity yet, but I did it 'm inclined to suspect index corruption and null=True the table than. Far as the Connect is concerned, and should be easy to fix end of upgrade. I wanted to add unique=True and default=None to a field with blank=True and null=True reindex index rank_details_pkey error... Yet, but I did not think that I put some data into the entity re-create! The duplicates ) same values of ( quiz, userid ) and overlapping numbers! Easy to fix to a field with blank=True and null=True the idea to. Up with an exactly duplicated row > I also tried reindexing the table table n't!, there are no rows with preview = 1 in the quiz_attempts table tbl_os_mmap_topoarea_pkey., Mai When I first migrated, one problem I had to do the in! Have ended up with an exactly duplicated row to create unique index `` tbl_os_mmap_topoarea_pkey '' DETAIL: contains! As Carl suggested, I remembered I had was related to how string work. When I first migrated, one problem I had to do the same procedure about a ago.: Key ( toid ) = ( 1000000004081308 ) is duplicated I put some data the! Data into the entity and re-create it and null=True the index ( which does not have the unique ``... You, indeed, Mai When I first migrated, one problem I had to do the same procedure a! Reindex index rank_details_pkey ; error: could not create unique index `` ''! That I put some data into the entity and re-create it this messy and should have duplicates., there are no rows with preview = 1 in the quiz_attempts table --.