Установить пустые строки ('') в NULL во всей базе данных

В моей базе данных много текстовых столбцов, где значения являются пустыми строками (''). Пустые строки должны быть установлены вNULL, Я не знаю точных схем, таблиц и столбцов в этой базе данных, или, скорее, я хочу написать общее решение, которое можно использовать повторно.

Как написать запрос / функцию, чтобы найти все текстовые столбцы во всех таблицах во всех схемах и обновить все столбцы пустыми строками ('') чтобыNULL?

 Jim Nasby19 июл. 2016 г., 15:31
Вам нужно будет создать настоящие операторы UPDATE, посмотрев, что находится в information_schema.columns. Функция format () может сделать это намного проще, чем куча строковых конкататов.

Ответы на вопрос(1)

Решение Вопроса

Запустить одинUPDATE за столомОбновлять только пустые столбцы (не определено)NOT NULL) с любой фактической пустой строкой.Обновляйте только строки с любой фактической пустой строкой.Оставьте другие значения без изменений.

Этот связанный ответ имеет функцию plpgsql, которая создает и запускаетUPDATE команда с использованием системного каталогаpg_attribute автоматически и безопасно для любой таблицы:

Заменить пустые строки с нулевыми значениями

Используя функцию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 привилегии на все выбранные таблицы, конечно.

В качестве устройства безопасности детей я прокомментировал полезную нагрузку.

Возможно, вы заметили, что я использую системные каталоги напрямую, а не информационную схему (которая тоже будет работать). Об этом:

Как проверить, существует ли таблица в данной схемеЗапрос на возврат имен выходных столбцов и типов данных запроса, таблицы или представленияДля многократного использования

Вот комплексное решение для многократного использования. Без предохранительных устройств:

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

Рассматривать:

Имя таблицы как параметр функции PostgreSQL

Осторожный! То же предупреждение, что и выше.
Также рассмотрите основное объяснение в ответе, который я связал выше:

Заменить пустые строки с нулевыми значениями
 Manquer20 июл. 2016 г., 08:42
Я пробовал нечто подобное с представлением information_schema.columns ... просто любопытно, есть ли какое-то преимущество в прямом использовании каталогов?
 Manquer20 июл. 2016 г., 08:27
спасибо .. это именно то, что я искал .. Конечно, такое масштабное обновление должно быть осуществлено тщательно. К счастью, это для использования в конвейере ETL, а не в основной базе данных OLTP, поэтому нюкинг по ошибке не будет такой большой головной болью :)
 Erwin Brandstetter20 июл. 2016 г., 17:27
@Manquer: одна из причин: информационная схема не содержит oids. Есть плюсы и минусы. Я добавил еще одну ссылку с подробной оценкой.

Ваш ответ на вопрос