6

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?

0

3 Answers 3

20

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:

0
2

SQL-99 DOMAIN may be a good solution to this problem.

Create a domain in PostgreSQL with a constraint for your uppercase requirement—and other requirements:

CREATE DOMAIN uctext AS TEXT
  CONSTRAINT uctext_uppercase CHECK (UPPER(VALUE) = VALUE)
  CONSTRAINT uctext_not_emtpy CHECK (LENGTH(VALUE) > 0);

Then define columns using your domain where required:

CREATE TABLE Products (
  product_code uctext NOT NULL UNIQUE,
  ...
);

The DB, without much effort, will enforce uppercase for such columns. But, in the case above, you must remember that the UNIQUE index, and the column in general, only contains uppercase data. Therefore, it is the application's responsibility to uppercase all comparison data used to match. For example, the following could cause your future self, or your successors, to wonder why products stick around after deleting them:

DELETE FROM Products WHERE product_code = 'valid-code-123';

Instead, you will need to do:

DELETE FROM Products WHERE product_code = UPPER('valid-code-123');

or create a DB API (function, procedure, trigger, etc.) to handle the uppercase. Also, all inserts will fail unless the data is transformed to uppercase prior.

0
0

Apologies for the late reply as I am very new to this site.

I liked the above answers. However, I used simple and dirty way as I have less tables & only few columns we need in upper case which are used to display for end users.

Generally, you need UPPER CASE for displaying the data to the end user. If so,

STEP 1: identify the tables & columns and use below SQL (this is for POSTGRES):

update TABLE1 set COLUMN_NAME = Upper(COLUMN_NAME);
update TABLE2 set COLUMN_NAME = Upper(COLUMN_NAME);
update TABLE3 set COLUMN_NAME = Upper(COLUMN_NAME);
so on...

STEP 2: Automate the above script(s) using CRONJOB or similar (may be every 30 mins or 1 hour or even every min)

btw, TRIGGERS (for updates) CHECK constraints (for forcing) may be best solutions for REAL-TIME as explained by other friends above but you need to define them for every column.

1
  • 1
    Just an FYI, this solution requires you to update the cron job every time you make a change to the database structure. Any new member of the team working on that database would need to be aware of that requirement.
    – Hannah Vernon
    Mar 2, 2021 at 16:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.