CODEWITHSUNDEEP
databasepostgresql
Manish Kumar
Manish Kumar

Reputation: 10502

Check that a given data is non empty regardless of its data type?

In PostgreSQL is there a way we can check that a given data is non empty regardless of its data type. I don't think following will work for all data types:

    IF table.columnName IS NOT NULL THEN
    END IF;

Upvotes: 0

Views: 63

Answers (2)

Big B
Big B

Reputation: 11

In the case of a character based column, an empty string IS NOT NULL.

To turn an empty string to NULL you can use: http://www.postgresqltutorial.com/postgresql-nullif/

NULLIF(table.columnName, '')

Depending on how crazy you want to get in perfection, here are additional resources you can use.

PostgreSQL provides three primary character types: character(n) or char(n), character varying(n) or varchar(n), and text, where n is a positive integer.

http://www.postgresqltutorial.com/postgresql-data-types/

For all other types, "NULL" is the only case when the data type is empty.

To check the type: How to get a list column names and datatype of a table in PostgreSQL?

SELECT
    "pg_attribute".attname                                                    as "Column",
    pg_catalog.format_type("pg_attribute".atttypid, "pg_attribute".atttypmod) as "Datatype",

    not("pg_attribute".attnotnull) AS "Nullable"
FROM
    pg_catalog.pg_attribute "pg_attribute"
WHERE
    "pg_attribute".attnum > 0
    AND NOT "pg_attribute".attisdropped
    AND "pg_attribute".attrelid = (
        SELECT "pg_class".oid
        FROM pg_catalog.pg_class "pg_class"
            LEFT JOIN pg_catalog.pg_namespace "pg_namespace" ON "pg_namespace".oid = "pg_class".relnamespace
        WHERE
            "pg_namespace".nspname = 'schema'
            AND "pg_class".relname = 'table'
    );

You can tweak the above to select only character based types.

To get all the empty strings, here is a function by Erwin that gives all the columns with empty values: https://dba.stackexchange.com/questions/81966/check-whether-empty-strings-are-present-in-character-type-columns

CREATE OR REPLACE FUNCTION f_empty_status(_tbl regclass, _col colclass)
  RETURNS 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;

Upvotes: 1

Georgi Raychev
Georgi Raychev

Reputation: 1334

IS NOT NULL works on all data types.

From the manual:

Comparison operators are available for all relevant data types.

Upvotes: 1

Related Questions