CODEWITHSUNDEEP
sqlstringpostgresqlunique-constraintcheck-constraints
Kartikeya
Kartikeya

Reputation: 53

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?

Upvotes: 0

Views: 1355

Answers (2)

Laurenz Albe
Laurenz Albe

Reputation: 246728

One option would be a unique index as detailed by GMB's answer.

The other options are:

  • Use the citext extension (case insensitive text) and use the citext data type. Then you can use a simple unique constraint.

  • With PostgreSQL v12 or better, create a case insensitive ICU collation:

    CREATE COLLATION german_ci (
   LOCALE = 'de_AT@colStrength=secondary',
   PROVIDER = 'icu',
   DETERMINISTIC = FALSE
);

    That example is for Austrian German, use the language of your choice or use und for a language agnostic collation (as far as such a thing exists).

    Then define the column as colname text COLLATE german_ci UNIQUE, using a regular unique constraint.

Upvotes: 0

GMB
GMB

Reputation: 222482

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))
)

Upvotes: 3

Related Questions