PostgreSQL force upper case for all data

Is there a way to force all text data to uppercase without recurring to writing a function for every table or doing it client side?

Forcing correct values is one thing.

A simple CHECK constraint per column can do the job reliably:

CREATE TABLE foo
   foo_id serial PRIMARY KEY
 , text_column text CHECK (upper(text_column) = text_column)
 , ...
);

Auto-correcting all input is another thing, and not as simple.

It can be done with a trigger using a trigger function that looks up column names and data types in the system catalog dynamically and converts all character data to upper case. Safe enough, but not quite as fast and bullet-proof. And triggers can be circumvented or counteracted by other triggers.

One generic trigger function for all:

CREATE OR REPLACE FUNCTION trg_all_upper()
  RETURNS trigger
  LANGUAGE plpgsql AS
$func$
DECLARE
   -- basic character types, possibly add citext, domains or custom types
   _typ   CONSTANT regtype[] := '{text, bpchar, varchar}';
   _sql   text;
   _found bool;
BEGIN
   SELECT INTO _sql, _found
          'SELECT ' || string_agg(
              CASE WHEN a.atttypid = ANY(_typ)
              THEN format ('upper(%1$s)::%2$s AS %1$s'
                         , a.col, a.atttypid::regtype)
              ELSE col END
            , ', ') || ' FROM (SELECT ($1).*) t'
        , bool_or(a.atttypid = ANY(_typ))
   FROM  (
      SELECT a.atttypid, quote_ident(attname) AS col
      FROM   pg_attribute a
      WHERE  a.attrelid = TG_RELID  -- object ID of table that fired trigger 
      AND    a.attnum >= 1          -- exclude tableoid & friends
      AND    NOT a.attisdropped     -- exclude dropped columns
      ORDER  BY a.attnum
      ) a;

   -- RAISE NOTICE '%', _sql;  -- debug
   IF _found THEN
      EXECUTE _sql USING NEW INTO NEW;
   END IF;

   RETURN NEW;
END
$func$;

A trigger per table:

CREATE TRIGGER all_upper_bef_insupd
BEFORE INSERT OR UPDATE ON big
FOR EACH ROW EXECUTE PROCEDURE trg_all_upper();

In Postgres 11 or later use the more sensible syntax:

...
FOR EACH ROW EXECUTE FUNCTION trg_all_upper();

Converts all values in columns with a character data type as defined in _typ to upper case.

Tested in Postgres 9.4.

Related:

• Create Alias for PostgreSQL Table

• Replace empty strings with null values

• How to set value of composite variable field using dynamic SQL

StackExchange Database Administrators Q#102957, answer score: 22