我正在按计数将一个巨大的表汇总到一个新表中,在这里我想将所有空字符串都更改为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.
更多推荐
用空值替换空字符串
发布评论