Is it possible to force values of a column to be lowercase

Question

I'm working with PostgreSQL 9.3.

I have a table with a varchar column that I always want to be filled with lowercase strings.

I could use the Postgres lower function before saving my values, but is there instead a way to define this column as a lowercase column?

You could define the field as case-insensitive text (CITEXT) which isn't what you asked for but might give you what you want. — user1596371
– user1596371, Commented Mar 29, 2015 at 15:33
If I do so, add a unique constraint and try to save 'AaaA' then 'aaaa', will I get a unique exception? — Simon
– Simon, Commented Mar 29, 2015 at 15:37
Yes you will if you create the field as UNIQUE e.g. create table t (f citext unique); — user1596371
– user1596371, Commented Mar 29, 2015 at 15:50
You can create a trigger that converts any input to lowercase — user330315
– user330315, Commented Mar 29, 2015 at 16:53

Simo Kivistö · Accepted Answer · 2015-03-29 16:41:35Z

You can accomplish this with a simple check in the column:

create table lower_field ( field1 varchar check (field1 = lower(field1)) );

1 Answer 1