解决方法
>每个表运行一个UPDATE.
>仅使用任何实际的空字符串更新可空列(未定义NOT NULL).
>仅更新包含任何实际空字符串的行.
>保持其他值不变.@H_301_3@
这个相关的答案有一个plpgsql函数,可以为任何给定的表自动安全地使用系统目录pg_attribute构建和运行UPDATE命令:@H_301_3@
> Replace empty strings with null values@H_301_3@
使用此答案中的函数f_empty2null(),您可以像这样循环选定的表:@H_301_3@
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$;
小心!这将更新数据库中所有用户表的所有列中的所有空字符串.确保这是你想要的,或者它可能会破坏你的数据库.@H_301_3@
当然,您需要对所有选定的表具有UPDATE权限.@H_301_3@
您可能已经注意到我直接使用系统目录,而不是信息模式(也可以使用).对这个:@H_301_3@
> 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@H_301_3@
供重复使用@H_301_3@
这是一个重复使用的集成解决方案.没有安全装置:@H_301_3@
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;
呼叫:@H_301_3@
SELECT * FROM pg_temp.f_all_empty2null();
返回:@H_301_3@
_tables | _rows ---------+--------- 23 | 123456
请注意我是如何正确地转义表名和列名的!@H_301_3@
c.oid::regclass AS tbl,quote_ident(attname) AS col
考虑:@H_301_3@
> Table name as a PostgreSQL function parameter@H_301_3@