Verifique se strings vazias estão presentes em colunas de tipo de caractere

A melhor maneira de fazer isso é com uma CHECKrestrição, como você observou, possivelmente por meio de um DOMAIN, por exemplo

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

em seguida, ALTERcolunas existentes para usar o domínio.

Se isso não for possível, você precisará consultar INFORMATION_SCHEMApara localizar todas as colunas do tipo de destino em todas as tabelas e, para cada coluna, gerar dinamicamente uma consulta para verificação. Você pode usar PL/PgSQL e a EXECUTEinstrução para isso; há muitos exemplos de seu uso dessa maneira em outro lugar no Stack Overflow.

Você não pode escrever uma única consulta para fazer o que deseja. Simplesmente não vai funcionar. Você terá que usar a geração de consulta de information_schema.

Função para uma única tabela

Retorna todas as colunas de tipo de caractere da tabela fornecida com uma contagem de valores vazios ( '') e se eles estão definidos NOT NULL.

CREATE OR REPLACE FUNCTION f_tbl_empty_status(_tbl regclass)
  RETURNS TABLE (tbl text, col text, empty_ct bigint, not_null bool)
  LANGUAGE plpgsql AS
$func$
DECLARE
   _typ      CONSTANT regtype[] := '{text, bpchar, varchar}';  -- base char types
   _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$;

Ligar:

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

Retorna:

tbl   | col        | empty_ct | not_null
------+------------+----------+---------
tbl1  | txt        | 0        | f
tbl1  | vc         | 3        | f
tbl1  | "oDD name" | 7        | f

Funciona para Postgres 9.1 ou posterior.

Os nomes das tabelas de saída são automaticamente qualificados pelo esquema, se necessário, de acordo com o search_path.

Os nomes da tabela de saída e os nomes das colunas são escapados automaticamente, se necessário.

empty_cté a contagem de linhas onde o valor da coluna é a string vazia

not_nullinforma se a coluna está definida NOT NULL(portanto, você não pode converter possíveis strings vazias em NULL!)

O nome da tabela de entrada pode opcionalmente ser qualificado pelo esquema, caso contrário, o padrão é o atual search_path.

Sua função precisa de privilégios para realmente ler a tabela fornecida.

A função é altamente otimizada e executa apenas uma única varredura na tabela fornecida para verificar todas as colunas relevantes.

Deve ser seguro contra injeção de SQL.

Usando paralelo unnest()para simplificar um pouco o código complexo:

• Desaninhar vários arrays em linhas

Você estará interessado nesta resposta relacionada no SO para realmente substituir strings vazias - com mais explicações :

• Substituir espaços em branco por valores nulos

Corresponde também a sequências de apenas caracteres de espaço

Como você comentou , trim(s.col, ' ') = ''faz o trabalho muito bem. Mas aqui está um atalho:

s.col::char = ''

Como?
charé um alias para character(1), o tipo de preenchimento em branco raramente útil. Os valores são preenchidos com caracteres de espaço à direita até o especificador de comprimento (que 1, neste caso, é irrelevante). Espaços à direita são efetivamente insignificantes para este tipo. Então ' 'é o mesmo que ''ou ' '. Voilá. E sim, é mais rápido também, testei.

Para encontrar strings com apenas caracteres de espaço também (não outros espaços em branco!), adicione a conversão a estas linhas acima:

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

Função wrapper para relatar um esquema inteiro

CREATE OR REPLACE FUNCTION f_schema_empty_status(_sch text DEFAULT 'public')
  RETURNS TABLE (tbl text, col text, empty_ct bigint, not_null bool)
  LANGUAGE plpgsql 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$;

Ligar:

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

Retorna:

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

db<>fiddle aqui
_{Old sqlfiddle}