sql – 在整个数据库中将空字符串(”)设置为NULL

前端之家收集整理的这篇文章主要介绍了sql – 在整个数据库中将空字符串(”)设置为NULL前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。
在我的数据库中有许多文本列,其中值为空字符串(”).空字符串需要设置为NULL.我不知道这个数据库中的确切模式,表和列,或者我想写一个可以重用的通用解决方案.

如何编写查询/函数来查找所有模式中所有表中的所有文本列,并将空字符串(”)的所有列更新为NULL?

解决方法

实现这一目标的最有效方法

>每个表运行一个UPDATE.
>仅使用任何实际的空字符串更新可空列(未定义NOT NULL).
>仅更新包含任何实际空字符串的行.
>保持其他值不变.

这个相关的答案有一个plpgsql函数,可以为任何给定的表自动安全地使用系统目录pg_attribute构建和运行UPDATE命令:

> Replace empty strings with null values

使用此答案中的函数f_empty2null(),您可以像这样循环选定的表:

  1. DO
  2. $do$
  3. DECLARE
  4. _tbl regclass;
  5. BEGIN
  6. FOR _tbl IN
  7. SELECT c.oid::regclass
  8. FROM pg_class c
  9. JOIN pg_namespace n ON n.oid = c.relnamespace
  10. WHERE c.relkind = 'r' -- only regular tables
  11. AND n.nspname NOT LIKE 'pg_%' -- exclude system schemas
  12. LOOP
  13. RAISE NOTICE $$PERFORM f_empty2null('%');$$,_tbl;
  14. -- PERFORM f_empty2null(_tbl); -- uncomment to prime the bomb
  15. END LOOP;
  16. END
  17. $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

供重复使用

这是一个重复使用的集成解决方案.没有安全装置:

  1. CREATE OR REPLACE FUNCTION f_all_empty2null(OUT _tables int,OUT _rows int) AS
  2. $func$
  3. DECLARE
  4. _typ CONSTANT regtype[] := '{text,bpchar,varchar,\"char\"}';
  5. _sql text;
  6. _row_ct int;
  7. BEGIN
  8. _tables := 0; _rows := 0;
  9. FOR _sql IN
  10. 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 '))
  11. FROM (
  12. SELECT c.oid::regclass AS tbl,quote_ident(attname) AS col
  13. FROM pg_namespace n
  14. JOIN pg_class c ON c.relnamespace = n.oid
  15. JOIN pg_attribute a ON a.attrelid = c.oid
  16. WHERE n.nspname NOT LIKE 'pg_%' -- exclude system schemas
  17. AND c.relkind = 'r' -- only regular tables
  18. AND a.attnum >= 1 -- exclude tableoid & friends
  19. AND NOT a.attisdropped -- exclude dropped columns
  20. AND NOT a.attnotnull -- exclude columns defined NOT NULL!
  21. AND a.atttypid = ANY(_typ) -- only character types
  22. ORDER BY a.attnum
  23. ) t
  24. GROUP BY t.tbl
  25. LOOP
  26. EXECUTE _sql;
  27. GET DIAGNOSTICS _row_ct = ROW_COUNT; -- report nr. of affected rows
  28. _tables := _tables + 1;
  29. _rows := _rows + _row_ct;
  30. END LOOP;
  31. END
  32. $func$ LANGUAGE plpgsql;

呼叫:

  1. SELECT * FROM pg_temp.f_all_empty2null();

返回:

  1. _tables | _rows
  2. ---------+---------
  3. 23 | 123456

请注意我是如何正确地转义表名和列名的!

  1. c.oid::regclass AS tbl,quote_ident(attname) AS col

考虑:

> Table name as a PostgreSQL function parameter

小心!与上述相同的警告.
还要考虑我上面链接的答案中的基本解释:

> Replace empty strings with null values

猜你在找的MsSQL相关文章