Duplicate value in postgresql table

Question

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

Answer 1

If you try to change the value in a column that is part PRIMARY KEYor has a restriction UNIQUEand get this error there, you should find a conflicting row

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 restriction, check this post .

Answer 2

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

Answer 3

First of all, determine which fields in the table should be unique. It could be something marked as a primary key, a unique index based on one or more fields or a control constraint, again based on one or more fields.

Once you do this, look at what you are trying to insert and work out, whether it violates any unique rules.

And yes, SELECT statements will help you determine what is wrong here. Use them to determine if you can commit the row.