How to enforce lowercase for all data in specific column?

Question

I have this table.

CREATE TABLE users (
 id SERIAL PRIMARY KEY,
 name TEXT
);

I'm looking for a way to enforce lowercase for the name values.

For example, when I save a record using this command:

INSERT INTO users (name) VALUES ('JOHN');

or:

INSERT INTO users (name) VALUES ('John');

I want it to be saved as john without converting the case beforehand using my programming language i.e. JavaScript, Ruby and etc.

Is it possible to make use of LOWER() function inside this CREATE TABLE command?

Answer 1

You need a trigger for that:

create function make_lower_name() 
 returns trigger
as
$$
begin
 new.name := lower(new.name);
 return new;
end;
$$
language plpgsql;
create trigger ensure_lower_name_trg
 before update or insert on users
 for each row
 execute procedure make_lower_name();

• Zulhilmi Zainudin: Where should I run this? In psql console?
• user1822: Yes, it's two DDL statements. One to create the trigger function and one to create the trigger.
• Zulhilmi Zainudin: What if I want to apply this for many columns? Do I just need to add new.xxx := lower(new.xxx); between the begin and the end;?
• user1822: @ZulhilmiZainudin: yes
• Zulhilmi Zainudin: I got this error - pastebin.com/raw/Nc7cLXj9 - any idea?