I have a table that's had CONSTRAINT table_pkey PRIMARY KEY (id) since setup.

However, I recently found that I have around 100 duplicate rows (I ran select count(*) from (select count(*) from table group by id having count(*) > 1) as t1 as per this old stack post. However, I'm running Postgres 10.6, which should be far after that bug.
Any ideas?

  • 3
    The postgresql mailing lists will be the best place to trouble shoot this. You have either found a bug or have hardware issues. Check your backup regime is working too. – Richard Huxton Jul 4 '19 at 6:25
  • Has this been pg_upgrade'd from a previous version, or was it on v10 since inception? If the latter, what was the minor version at inception? – jjanes Jul 4 '19 at 16:11
  • @jjanes this is a brand new table (so thankfully I don't have to worry about backups). I'm assuming 10.6, though I suppose it could have been 10.5 – mckennab Jul 6 '19 at 5:08

I have some suggestions based on problems I had before, not with PostgreSQL 10, but with 9.1:

  • Try to reindex
  • Try vacuum full analyze verbose

If first two option don't work, try a dump and restore.

This happens because of a bug in indexes.

| improve this answer | |
  • thanks, any advice for how to keep duplication from happening again? I suppose besides checking if a row exists before insertion... – mckennab Jul 6 '19 at 5:14
  • Could you solve your problem ? I suggest you maintain maintenance routine constantly, and always update your PostgreSQL version. – Targino Silveira Jul 6 '19 at 13:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.