用空值替换空字符串

发布时间：2020-12-13 15:56:26 所属栏目：百科来源：网络整理

导读：我正在通过计数将一个巨大的表卷起来到一个新表中,我想将所有空字符串更改为NULL,并且还要对一些列进行类型转换.我阅读了一些帖子,我找不到一个查询,这可以让我在单个查询中的所有列中进行查询,而不使用多个语句. 让我知道是否可以遍历所有列并使用null替换

我正在通过计数将一个巨大的表卷起来到一个新表中,我想将所有空字符串更改为NULL,并且还要对一些列进行类型转换.我阅读了一些帖子,我找不到一个查询,这可以让我在单个查询中的所有列中进行查询,而不使用多个语句.

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

参考：How to convert empty spaces into null values,using SQL Server?

解决方法

据我所知,没有内置函数可以替换表中所有列的空字符串.你可以写一个 plpgsql function来照顾它.

以下函数用NULL替换给定表的所有基本字符类型列中的空字符串.如果剩余的字符串是有效的数字文字,则可以转换为整数.

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 Fiddle.

主要观点

>表名必须有效且可见,并且主叫用户必须具有所有必要的权限.如果不满足这些条件中的任何一个,则该功能将不起作用 – 即也没有任何东西可以被破坏.我投入到object identifier type regclass以确保它.
表名可以按原样提供(‘mytable’),然后search_path决定.或者模式限定选择某个模式(‘myschema.mytable’).
>查询系统目录以获取表的所有(character-type)列.提供的函数使用这些基本的character types：text,“char”.仅处理相关列.
>使用quote_ident()或format()清理列名称和safeguard against SQLi.
>更新后的版本使用基本的SQL聚合函数string_agg()来构建不带循环的命令字符串,这样更简单,更快捷.而且更优雅.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!