How to deal with Case Sensitivity in PostgreSQL

I have a master table for color codes which stores various unique color values. It has a column Color_value on which UNIQUE constraint has been applied. However it is accepting both ‘Black’ and ‘black’ values. How can such situations be handled?

Answer

You can create a unique index like this:

create unique index unique_color_value on colors(lower(color_value));

That said, it would be much simpler to make your data consistent from the start, by using a constraint that allows only lower case values in the column to start with.

create table colors (
    ...
    color_value text 
        unique 
        check(color_value = lower(color_value))
)