1

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?

Improve this question
3
  • 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, 2019 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, 2019 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, 2019 at 5:08

1 Answer 1

Reset to default
0

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
2
  • thanks, any advice for how to keep duplication from happening again? I suppose besides checking if a row exists before insertion...
    – mckennab
    Jul 6, 2019 at 5:14
  • Could you solve your problem ? I suggest you maintain maintenance routine constantly, and always update your PostgreSQL version.
    – Targino Castelo Branco
    Jul 6, 2019 at 13:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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