Styjun

Does an object always see its latest internal state irrespective of thread?

Is it tax fraud for an individual to declare non-taxable revenue as taxable income? (US tax laws)

Why does Kotter return in Welcome Back Kotter?

tikz convert color string to hex value

How much of data wrangling is a data scientist's job?

Why do I get two different answers for this counting problem?

Can a vampire attack twice with their claws using Multiattack?

Is it inappropriate for a student to attend their mentor's dissertation defense?

Alternative to sending password over mail?

Can you really stack all of this on an Opportunity Attack?

Today is the Center

Rock identification in KY

Replacing matching entries in one column of a file by another column from a different file

NMaximize is not converging to a solution

Why doesn't Newton's third law mean a person bounces back to where they started when they hit the ground?

What's the output of a record needle playing an out-of-speed record

Watching something be written to a file live with tail

Did Shadowfax go to Valinor?

What does it mean to describe someone as a butt steak?

Are the number of citations and number of published articles the most important criteria for a tenure promotion?

Why is 150k or 200k jobs considered good when there's 300k+ births a month?

When a company launches a new product do they "come out" with a new product or do they "come up" with a new product?

How can bays and straits be determined in a procedurally generated map?

Modeling an IP Address

Remove duplicate rows in table with dependent foreign keys

How to remove duplicate rows with foreign keys dependencies?Removing duplicate rows from table in OracleFinding duplicate values in a SQL tableHow to select rows with no matching entry in another table?Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraintsUpdating foreign keys while inserting into new tableForeign Key to non-primary keyRemove duplicate rows ERROR: duplicate key valueOverlapping Foreign Keys and Cascade On DeleteHow to get a count of rows (across tables) dependent on a particular column which is referenced as a foreign key in PostgresConstraints for foreign keys like this in associative table?

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty height:90px;width:728px;box-sizing:border-box;

0

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)

sql postgresql

share|improve this question

edited Mar 21 at 23:03

Bird

asked Mar 21 at 22:49

BirdBird

7073934

• 
  
  
  

  
  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 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 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 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 at 0: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.
  
  – Bird
  Mar 22 at 16:59
  

  
  
  
  

add a comment | 

0

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)

sql postgresql

share|improve this question

edited Mar 21 at 23:03

Bird

asked Mar 21 at 22:49

BirdBird

7073934

• 
  
  
  

  
  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 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 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 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 at 0: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.
  
  – Bird
  Mar 22 at 16:59
  

  
  
  
  

add a comment | 

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)

sql postgresql

share|improve this question

edited Mar 21 at 23:03

Bird

asked Mar 21 at 22:49

BirdBird

7073934

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

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)

share|improve this question

edited Mar 21 at 23:03

Bird

asked Mar 21 at 22:49

BirdBird

7073934

share|improve this question

edited Mar 21 at 23:03

Bird

asked Mar 21 at 22:49

BirdBird

7073934

asked Mar 21 at 22:49

BirdBird

7073934

asked Mar 21 at 22:49

BirdBird

7073934