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?
1
-
Oh, it wasn't me that gave it. You'd have to be deliberately malevolent in order to get me to give a downvote.Ignacio Vazquez-Abrams– Ignacio Vazquez-Abrams2010-01-16 11:52:53 +00:00Commented Jan 16, 2010 at 11:52
Add a comment
|
3 Answers
Try Like This
SELECT count(column_name), column_name
from table_name
group by column_name having count(column_name) > 1;
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.
1 Comment
kevoroid
i dont know the conflicting_value! i just "ERROR: duplicate key value violates unique constraint "user_audit_log_pkey"
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.
