用空值替换空字符串

本文介绍了用空值替换空字符串的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我正在按计数将一个巨大的表汇总到一个新表中，在这里我想将所有空字符串都更改为NULL，并且还要对某些列进行类型转换.我通读了一些帖子，但找不到查询，这使我可以在单个查询中跨所有列进行查询，而无需使用多条语句.

I am rolling up a huge table by counts into a new table, where I want to change all the empty strings to NULL, and typecast some columns as well. I read through some of the posts and I could not find a query, which would let me do it across all the columns in a single query, without using multiple statements.

让我知道是否可以遍历所有列，并用空字符串替换为null来替换单元格.

Let me know if it is possible for me to iterate across all columns and replace cells with empty strings with null.

参考:如何使用SQL Server将空白转换为空值?

推荐答案

据我所知，没有内置函数可以替换表中所有列的空字符串.您可以将 plpgsql函数 编写为照顾这个.

To my knowledge there is no built-in function to replace empty strings across all columns of a table. You can write a plpgsql function to take care of that.

以下函数用NULL替换给定表的所有基本字符类型列中的空字符串 .然后，如果其余字符串是有效的数字文字，则可以强制转换为integer.

The following function replaces empty strings in all basic character-type columns of a given table with NULL. You can then cast to integer if the remaining strings are valid number 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;

致电:

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

还要获取列名updated_rows:

SELECT * FROM f_empty2null('mytable');

SQL小提琴.

• 表名必须有效且可见，并且主叫用户必须具有所有必要的特权.如果不满足这些条件中的任何一个，则该函数将不执行任何操作-即，也不会破坏任何操作.我强制转换为对象标识符类型regclass 确定. 可以按原样('mytable')提供表名，然后默认提供 search_path 决定.或符合模式资格以选择特定模式('myschema.mytable').

• Table name has to be valid and visible and the calling user must have all necessary privileges. If any of these conditions are not met, the function will do nothing - i.e. nothing can be destroyed, either. I cast to the object identifier type regclass to make sure of it. The table name can be supplied as is ('mytable'), then the search_path decides. Or schema-qualified to pick a certain schema ('myschema.mytable').

查询系统目录以获取所有内容(字符-类型)列.提供的函数使用以下基本字符类型:text ，bpchar，varchar，"char".仅处理相关的列.

Query 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 relevant columns are processed.

使用 quote_ident() 或format()清除列名和防范SQLi .

更新的版本使用基本的SQL聚合函数string_agg()来构建命令字符串而不会循环，这更简单，更快捷.而且更优雅. :)

The updated version uses the basic SQL aggregate function string_agg() to build the command string without looping, which is simpler and faster. And more elegant. :)

必须使用具有EXECUTE 的动态SQL.

Has to use dynamic SQL with EXECUTE.

更新后的版本不包含定义为NOT NULL的列，并且仅在单个语句中更新 每行 ，这对于具有多个字符的表来说要快得多，键入列.

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

应与任何现代PostgreSQL版本一起使用.经过Postgres 9.1、9.3和9.5的测试.

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