解决方法
实现这一目标的最有效方法:
>每个表运行一个UPDATE.
>仅使用任何实际的空字符串更新可空列(未定义NOT NULL).
>仅更新包含任何实际空字符串的行.
>保持其他值不变.
这个相关的答案有一个plpgsql函数,可以为任何给定的表自动安全地使用系统目录pg_attribute构建和运行UPDATE命令:
> Replace empty strings with null values
使用此答案中的函数f_empty2null(),您可以像这样循环选定的表:
- DO
- $do$
- DECLARE
- _tbl regclass;
- BEGIN
- FOR _tbl IN
- SELECT c.oid::regclass
- FROM pg_class c
- JOIN pg_namespace n ON n.oid = c.relnamespace
- WHERE c.relkind = 'r' -- only regular tables
- AND n.nspname NOT LIKE 'pg_%' -- exclude system schemas
- LOOP
- RAISE NOTICE $$PERFORM f_empty2null('%');$$,_tbl;
- -- PERFORM f_empty2null(_tbl); -- uncomment to prime the bomb
- END LOOP;
- END
- $do$;
小心!这将更新数据库中所有用户表的所有列中的所有空字符串.确保这是你想要的,或者它可能会破坏你的数据库.
当然,您需要对所有选定的表具有UPDATE权限.
作为儿童安全设备,我评论了有效载荷.
您可能已经注意到我直接使用系统目录,而不是信息模式(也可以使用).对这个:
> How to check if a table exists in a given schema
> Query to return output column names and data types of a query,table or view
供重复使用
这是一个重复使用的集成解决方案.没有安全装置:
- CREATE OR REPLACE FUNCTION f_all_empty2null(OUT _tables int,OUT _rows int) AS
- $func$
- DECLARE
- _typ CONSTANT regtype[] := '{text,bpchar,varchar,\"char\"}';
- _sql text;
- _row_ct int;
- BEGIN
- _tables := 0; _rows := 0;
- FOR _sql IN
- SELECT format('UPDATE %s SET %s WHERE %s',t.tbl,string_agg(format($$%1$s = NULLIF(%1$s,'')$$,t.col),','),string_agg(t.col || $$= ''$$,' OR '))
- FROM (
- SELECT c.oid::regclass AS tbl,quote_ident(attname) AS col
- FROM pg_namespace n
- JOIN pg_class c ON c.relnamespace = n.oid
- JOIN pg_attribute a ON a.attrelid = c.oid
- WHERE n.nspname NOT LIKE 'pg_%' -- exclude system schemas
- AND c.relkind = 'r' -- only regular tables
- AND a.attnum >= 1 -- exclude tableoid & friends
- AND NOT a.attisdropped -- exclude dropped columns
- AND NOT a.attnotnull -- exclude columns defined NOT NULL!
- AND a.atttypid = ANY(_typ) -- only character types
- ORDER BY a.attnum
- ) t
- GROUP BY t.tbl
- LOOP
- EXECUTE _sql;
- GET DIAGNOSTICS _row_ct = ROW_COUNT; -- report nr. of affected rows
- _tables := _tables + 1;
- _rows := _rows + _row_ct;
- END LOOP;
- END
- $func$ LANGUAGE plpgsql;
呼叫:
- SELECT * FROM pg_temp.f_all_empty2null();
返回:
- _tables | _rows
- ---------+---------
- 23 | 123456
请注意我是如何正确地转义表名和列名的!
- c.oid::regclass AS tbl,quote_ident(attname) AS col
考虑:
> Table name as a PostgreSQL function parameter
小心!与上述相同的警告.
还要考虑我上面链接的答案中的基本解释: