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. — , Mar 29 '15 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, Mar 29 '15 at 15:37
Yes you will if you create the field as UNIQUE e.g. `create table t (f citext unique);` — , Mar 29 '15 at 15:50
You can create a trigger that converts any input to lowercase — , Mar 29 '15 at 16:53
Do you think that it is a better way to do what I need? (instead of create a "citext" field) — Simon, Mar 29 '15 at 17:16
[`citext`](http://www.postgresql.org/docs/current/interactive/citext.html) is probably better than any hand-knit solution - if the [limitations](http://www.postgresql.org/docs/current/interactive/citext.html#AEN155284) are not a problem for you. — Erwin Brandstetter, Mar 30 '15 at 00:42
Possible duplicate: http://stackoverflow.com/questions/7005302/postgresql-how-to-make-not-case-sensitive-queries — Erwin Brandstetter, Mar 30 '15 at 00:48

score 2 · Accepted Answer · answered Mar 29 '15 at 16:41

2

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

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

answered Mar 29 '15 at 16:41

Simo Kivistö

1 Answers1