Create a Postgres rule or trigger to automatically convert a column to lowercase or uppercase on insert

Question

How would you create a rule in postgres to force lowercase on X column on insert. EG When inserting:

INSERT INTO foobar (foo, bar) VALUES ('EXAMPLE', 2);

I would want EXAMPLE to be lowercased to example before persisting.

Answer 1

This solution has been tested and is working in Postgres 9.1

I used triggers to solve problem.

Here's the full code so you can paste into postgres and try it out yourself & explanation of how it works below

DROP TABLE foobar;
CREATE TABLE foobar (
foo text,
bar int
);

CREATE OR REPLACE FUNCTION lowecase_foo_on_insert() RETURNS trigger AS $lowecase_foo_on_insert$
    BEGIN        
        NEW.foo = LOWER(NEW.foo);
        RETURN NEW;
    END;
$lowecase_foo_on_insert$ LANGUAGE plpgsql;

CREATE TRIGGER lowecase_foo_on_insert_trigger BEFORE INSERT OR UPDATE ON foobar
    FOR EACH ROW EXECUTE PROCEDURE lowecase_foo_on_insert();

INSERT INTO foobar (foo, bar) VALUES ('LOWERCASE ME', 1);

SELECT * FROM foobar; //result 'lowercase me'

Create our demo table:

CREATE TABLE foobar (
    foo text,
    bar int
);

Create function that converts (foo) to lowercase:

CREATE OR REPLACE FUNCTION lowecase_foo_on_insert() RETURNS trigger AS $lowecase_foo_on_insert$
    BEGIN        
        NEW.foo = LOWER(NEW.foo);
        RETURN NEW;
    END;
$lowecase_foo_on_insert$ LANGUAGE plpgsql;

Create a trigger that executes code to lowercase foo before insert:

CREATE TRIGGER lowecase_foo_on_insert_trigger BEFORE INSERT OR UPDATE ON foobar
    FOR EACH ROW EXECUTE PROCEDURE lowecase_foo_on_insert();

And now to test our work:

INSERT INTO foobar (foo, bar) VALUES ('LOWERCASE ME', 1);
SELECT * FROM foobar;

Result is that the only row in column foo is now set to 'lowercase me'