1

Somehow a table in my PostgreSQL database had every row duplicated, so there are now two rows for every id (primary key -- see attached sample image below). Another column in that table, FAN is a foreign key in another table holdings. When I tried to delete the duplicates in table surveys with:

DELETE
FROM
    surveys a
        USING surveys b
WHERE
    a.id = b.id;

I got the error:

ERROR: update or delete on table "surveys" violates foreign key constraint holdings_FAN_id_ca1342a3_fk_surveys_id" on table "holdings"

I tried the solution to this SO question, but the the output was just an empty id column (no rows, no values), and the original surveys table was unchanged. I'm new to SQL and don't fully understand what was going on in those commands, so I may have missed something.

How can I remove the duplicate rows in my table?

Using PostgreSQL 9.6.10. Tables were created with Django models (sample code added based on comment):

class surveys(models.Model):    
    FAN = models.SlugField(max_length=100, blank=True, null=True)
class holdings(models.Model):
    FAN = models.ForeignKey('surveys', on_delete=models.SET_NULL, blank=True, null=True)

enter image description here

Evan
  • 1,960
  • 4
  • 26
  • 54
  • 1
    Duplicated values in a primary key column are basically not possible in SQL. You might want to update your question with sample data and expected output to clarify it a little. – GMB Mar 21 '19 at 22:57
  • Instead of *talking about your table definition* you should provide the `CREATE TABLE` statement defining it. And always your version of Postgres. – Erwin Brandstetter Mar 21 '19 at 22:58
  • 2
    A PK column with duplicate entries? I have never seen such a thing before. Looks like your database is seriously broken. Make a backup if you can ASAP ... – Erwin Brandstetter Mar 21 '19 at 23:01
  • 1
    I wonder, if OP just assumes there are duplicates in the data. I assume instead, that the *query* produces duplicates (probably some join and no `distinct` or something.) please check the query that produces your screenshotted result set (and post it perhaps). try `distinct` immediately after the `select` keyword. as erwin notes correctly, primary keys can't have duplicates, by definition. – Jakumi Mar 22 '19 at 00:29
  • The screenshot was just from the view data button for that table in pgAdmin III. I have reason to believe it wasn't just a display issue or an assumption because the Django site that uses it raised an error that there were two values per id. So, it seems to somehow be possible to create duplicate primary keys in postgresql. – Evan Mar 22 '19 at 16:59

0 Answers0