CODEWITHSUNDEEP
sqlpostgresql
kevoroid
kevoroid

Reputation: 5302

Duplicate value in a postgresql table

I'm trying to modify a table inside my PostgreSQL database, but it says there is duplicate! what is the best way to find a duplicate value inside a table? kinda a select query?

Upvotes: 4

Views: 7948

Answers (3)

cserepj
cserepj

Reputation: 926

Try Like This 

SELECT count(column_name), column_name 
from table_name 
group by column_name having count(column_name) > 1;

Upvotes: 14

Peter Lang
Peter Lang

Reputation: 55524

If you try to change a value in a column that is part of the PRIMARY KEY or has a UNIQUE constraint and get this error there, then you should be able to find the conflicting row by

SELECT *
FROM your_table
WHERE conflicting_column = conflicting_value;

If conflicting_value is a character type, put it in single quotes (').

EDIT: To find out which columns are affected by the constraint, check this post.

Upvotes: 2

Paul Alan Taylor
Paul Alan Taylor

Reputation: 10680

First of all, determine which fields in your table have to be unique. This may be something marked as a Primary Key, a unique index based on one or more fields or a check constraint, again based on one or more fields.

Once you've done that, look at what you're trying to insert and work out whether it busts any of the unique rules.

And yes, SELECT statements will help you determine what's wrong here. Use those to determine whether you are able to commit the row.

Upvotes: 0

Related Questions