6

I have an application that (as part of its logic) trims strings and replaces empty strings with NULL prior to insertion in the database. I guess one way to ensure that this is enforced would be to write a CHECK on every table that has a VARCHAR, TEXT (or similar) column.

Assuming that one can't or does not want to do that is there a way to write a simple, generic, SQL query (obtaining table and column names from the database's metadata) that would check if any textual columns in the database contain empty strings?

3

Function for a single table

Returns all character-type columns of the given table with a count of empty values ('') and whether they are defined NOT NULL.

CREATE OR REPLACE FUNCTION f_tbl_empty_status(_tbl regclass)
  RETURNS TABLE (tbl text, col text, empty_ct bigint, not_null bool) AS
$func$
DECLARE
   -- basic char types, possibly extend with citext, domains or custom types:
   _typ      CONSTANT regtype[] := '{text, bpchar, varchar, "\"char\""}';
   _sql      text;
   _col_arr  text[];
   _null_arr bool[];
BEGIN

-- Build command
SELECT INTO _col_arr, _null_arr, _sql
       array_agg(s.col)
     , array_agg(s.attnotnull)
     , '
SELECT $1
      ,unnest($2)
      ,unnest(ARRAY [count('
              || string_agg(s.col, ' = '''' OR NULL), count(')
                                || ' = '''' OR NULL)])
      ,unnest($3)
FROM   ' || _tbl
FROM  (
   SELECT quote_ident(attname) AS col, attnotnull
   FROM   pg_attribute
   WHERE  attrelid = _tbl              -- valid, visible, legal table name 
   AND    attnum >= 1                  -- exclude tableoid & friends
   AND    NOT attisdropped             -- exclude dropped columns
-- AND    NOT attnotnull               -- include columns defined NOT NULL
   AND    atttypid = ANY(_typ)         -- only character types
   ORDER  BY attnum
   ) AS s;

-- Debug
-- RAISE NOTICE '%', _sql;

-- Execute
IF _sql IS NULL THEN
   -- do nothing, nothing to return
ELSE
   RETURN QUERY EXECUTE _sql
   USING  _tbl::text, _col_arr, _null_arr;
END IF;

END
$func$  LANGUAGE plpgsql;

Call:

SELECT * FROM f_tbl_empty_status('tbl_name'); -- optionally schema-qualified

Returns:

tbl   | col        | empty_ct | not_null
------+------------+----------+---------
tbl1  | txt        | 0        | f
tbl1  | vc         | 3        | f
tbl1  | "oDD name" | 7        | f
  • Works for Postgres 9.1 or later.

  • Output table names are automatically schema-qualified if needed, according to the current search_path.

  • Output table names and column names are automatically escaped if needed.

  • empty_ct is the count of rows where the value of the column is the empty string

  • not_null reports whether the column is defined NOT NULL (so you cannot convert possible empty strings to NULL!)

  • Input table name can optionally be schema-qualified, else defaults to the current search_path.

  • Your role needs privileges to actually read from the given table.

  • The function is highly optimized and only runs a single scan on the given table to check on all relevant columns.

  • Should be safe against SQL injection.

  • Using parallel unnest() to simplify the complex code somewhat:

  • You will be interested in this related answer on SO to actually replace empty strings - with more explanation:

Match strings of only space characters, too

As you commented, trim(s.col, ' ') = '' does the job just fine.
But I here's a shortcut:

s.col::char = ''

How?
char is an alias for character(1), the rarely useful blank-padded type. Values are padded with space characters to the right up to the length specifier (which is 1 in this case, but irrelevant). Trailing spaces are effectively insignificant for this type. So ' ' is the same as '' or ' '. Voilá. And yes, it is faster, too. I tested.

To find strings of only space characters, too (not other white space!), add the cast to these lines above:

                  || string_agg(s.col, '::char = '''' OR NULL), count(')
                                    || '::char = '''' OR NULL)])

Wrapper function to report on a whole schema

CREATE OR REPLACE FUNCTION f_schema_empty_status(_sch text DEFAULT 'public')
  RETURNS TABLE (tbl text, col text, empty_ct bigint, not_null bool) AS
$func$
DECLARE 
   _tbl regclass;
BEGIN

FOR _tbl IN
   SELECT c.oid
   FROM   pg_class c
   JOIN   pg_namespace n ON n.oid = c.relnamespace
   WHERE  n.nspname = _sch  -- 'public' by default
   -- AND    c.relname LIKE 'my_pattern%'  -- optionally filter table names
   AND    c.relkind = 'r'  -- regular tables only
   ORDER  BY relname
LOOP
   -- Debug
   -- RAISE NOTICE 'table: %', _tbl;

   RETURN QUERY
   SELECT * FROM f_tbl_empty_status(_tbl);
END LOOP;

END
$func$  LANGUAGE plpgsql;

Call:

SELECT * FROM f_schema_empty_status();  -- defaults to 'public' without parameter

Returns:

tbl   | col        | empty_ct | not_null
------+------------+----------+---------
tbl1  | txt        | 0        | f
tbl1  | vc         | 3        | f
tbl1  | "oDD name" | 7        | f
tbl2  | some_text  | 123      | t
...

SQL Fiddle.

2
  • Awesome. Just added a trim: ,unnest(ARRAY [count(trim('|| string_agg(s.col, ') = '''' OR NULL), count(trim(') || ') = '''' OR NULL)]) ... to also capture whitespace (I hadn't explicitly mentioned whitespace in my question). Nov 6 '14 at 15:47
  • @MarcusJuniusBrutus: I have one more magic trick. Consider the added bit about s.col::char = '' Nov 6 '14 at 23:47
3

The best way to do this is with a CHECK constraint, as you noted, possibly via a DOMAIN, e.g.

CREATE DOMAIN nonempty_string AS text 
CONSTRAINT non_empty CHECK (length(VALUE) > 0);

then ALTER existing columns to use the domain.

If that's not possible, you will need to query INFORMATION_SCHEMA to find all columns of the target type across all tables, then for each column, dynamically generate a query to check. You can use PL/PgSQL and the EXECUTE statement for this; there are many examples of its usage this way elsewhere on Stack Overflow.

You cannot write a single query to do what you want. It just won't work. You'll have to use query generation from information_schema.

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.