Replace empty strings with null values

Question

I am assembling a huge table by counting into a new table, where I want to change all empty rows to NULL , and also output some columns. I read some of the posts, and I could not find a query that would allow me to do this through all the columns in one query, without using multiple operators.

Let me know if it is possible for me to iterate over all columns and replace cells with empty rows with zero value.

Link: How to convert empty spaces to null values ​​using SQL Server?

Answer 1

As far as I know, there is no built-in function to replace empty rows in all columns of a table. You can write the plpgsql function to take care of this.

The following function replaces empty rows in all columns of the base character type of this table with NULL . Then you can apply to integer if the remaining lines are valid numeric literals.

 CREATE OR REPLACE FUNCTION f_empty2null(_tbl regclass, OUT updated_rows int) AS $func$ DECLARE -- basic char types, possibly extend with citext, domains or custom types: _typ CONSTANT regtype[] := '{text, bpchar, varchar, \"char\"}'; _sql text; BEGIN SELECT INTO _sql -- build command format('UPDATE %s SET %s WHERE %s' , _tbl , string_agg(format($$%1$s = NULLIF(%1$s, '')$$, col), ', ') , string_agg(col || $$ = ''$$, ' OR ')) FROM ( SELECT quote_ident(attname) AS col 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 -- exclude columns defined NOT NULL! AND atttypid = ANY(_typ) -- only character types ORDER BY attnum ) sub; -- Test -- RAISE NOTICE '%', _sql; -- Execute IF _sql IS NULL THEN updated_rows := 0; -- nothing to update ELSE EXECUTE _sql; GET DIAGNOSTICS updated_rows = ROW_COUNT; -- Report number of affected rows END IF; END $func$ LANGUAGE plpgsql; 

Call:

 SELECT f_empty2null('mytable'); SELECT f_empty2null('myschema.mytable'); 

To get the updated_rows column name:

 SELECT * FROM f_empty2null('mytable'); 

SQL Fiddle

Highlights

• The table name must be valid and visible, and the calling user must have all the necessary privileges. If any of these conditions is not met, the function will do nothing - that is, nothing can be destroyed. I passed the identifier type of the regclass object to verify this.
  The table name can be represented as ( 'mytable' ), then search_path is search_path . Or a schema suitable for selecting a specific schema ( 'myschema.mytable' ).

• Request the system catalog to get all ( character-type ) columns of the table. The provided function uses these basic character types : text , bpchar , varchar , "char" . Only the corresponding columns are processed.

• Use quote_ident() or format() to sanitize column names and protect against SQLi .

• The updated version uses the basic SQL aggregation function string_agg() to create a command line without a loop, which is simpler and faster. And more elegant. :)

• You must use dynamic SQL with EXECUTE .

• The updated version excludes certain NOT NULL columns and updates each row once in a single expression , which is much faster for tables with multiple character-type columns.

• Should work with any modern version of PostgreSQL. Tested with Postgres 9.1, 9.3 and 9.5.