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