Приведение типа NULL при обновлении нескольких строк

Question

У меня проблема, когда я пытаюсь обновить несколько строк одновременно.

Вот таблица и запрос, которые я использую (упрощено для лучшего чтения):

Таблица

CREATE TABLE foo
(
    pkid integer,
    x integer,
    y integer
)

запрос

UPDATE foo SET x=t.x, y=t.y FROM
(VALUES (50, 50, 1),
        (100, 120, 2))
AS t(x, y, pkid) WHERE foo.pkid=t.pkid

Этот запрос работает отлично, но когда я пытаюсь выполнить запрос, в котором все значения x или y равны нулю, я получаю сообщение об ошибке:

запрос с нулями

UPDATE foo SET x=t.x, y=t.y FROM
(VALUES (null, 20, 1),
        (null, 50, 2))
AS t(x, y, pkid) WHERE foo.pkid=t.pkid

ошибка

ERROR:  column "x" is of type integer but expression is of type text
LINE 1: UPDATE foo SET x=t.x FROM

Единственный способ исправить это - изменить хотя бы одно из значений. (null, 20, 1) в (null:int, 50, 2) но я не могу этого сделать, так как у меня есть функция, которая генерирует запрос "обновить несколько строк", и она ничего не знает о типах столбцов.

Какое лучшее решение здесь? Есть ли лучший запрос на обновление для нескольких строк? Есть ли какая-либо функция или синтаксис, как AS t(x:gettype(foo.x), y:gettype(foo.y), pkid:gettype(foo.pkid))?

Answer 1

С автономным VALUES Выражение PostgreSQL не знает, какими должны быть типы данных. С помощью простых числовых литералов система с удовольствием принимает подходящие типы. Но с другим вкладом (как NULL) вам нужно было бы привести в явном виде - как вы уже узнали.

Вы можете запросить pg_catalog (быстрый, но специфичный для PostgreSQL) или information_schema (медленный, но стандартный SQL), чтобы выяснить и подготовить ваше утверждение с соответствующими типами.

Или вы можете использовать один из этих простых "трюков" (я сохранил лучшее для последнего):

1. Выберите строку с помощью LIMIT 0, добавить строки с UNION ALL

UPDATE foo f
SET    x = t.x
     , y = t.y
FROM  (
  (SELECT pkid, x, y FROM foo LIMIT 0) -- parenthesis needed with LIMIT
   UNION ALL
   SELECT 1, 20, NULL
   UNION ALL
   SELECT 2, 50, NULL
   ) t               -- column names and types are already defined
WHERE  f.pkid = t.pkid

Первый вложенный выбор подзапроса:

(SELECT x, y, pkid  FROM foo LIMIT 0)

получает имена и типы для столбцов, но LIMIT 0 предотвращает добавление фактической строки. Последующие строки приводятся к теперь четко определенному типу строки и немедленно проверяются, соответствуют ли они типу. Должно быть небольшое дополнительное улучшение по сравнению с вашей первоначальной формой.

Основное ограничение: с отдельным SELECT Строки Postgres немедленно преобразует входные литералы в тип "лучшее из возможного". Когда он позже пытается привести к данным типам первого SELECTможет быть уже слишком поздно для некоторых типов, если между предполагаемым типом и целевым типом нет приведенного зарегистрированного присвоения. пример text -> timestamp,

Pro:
- Минимальные накладные расходы.
- Читаемый, простой и быстрый для нескольких строк.
- Вам нужно только знать соответствующие имена столбцов таблицы.

Против:
- Разрешение типа может не работать для некоторых типов.
- UNION ALL SELECT медленнее, чем VALUES выражение для длинных списков строк, как вы нашли в своем тесте.
- Подробный синтаксис в строке.

2. VALUES выражение с типом для каждого столбца

...
FROM  (
   VALUES 
     ((SELECT pkid FROM foo LIMIT 0)
    , (SELECT x    FROM foo LIMIT 0)
    , (SELECT y    FROM foo LIMIT 0))  -- get type for each col individually
   , (1, 20, NULL)
   , (2, 50, NULL)
   ) t (pkid, x, y)  -- columns names not defined yet, only types.
...

Первый ряд в VALUES выражение это ряд NULL значения, которые определяют тип для всех последующих строк.

Pro:
- Быстрее, чем 1.
- Кратчайший синтаксис для таблиц с множеством столбцов и только немногие из них актуальны.
- Вам нужно только знать соответствующие имена столбцов таблицы.

Против:
- подробный синтаксис только для нескольких строк
- Менее читаемый (ИМО).

3. VALUES выражение с типом строки

UPDATE foo f
SET x = (t.r).x       -- parenthesis needed to make syntax unambiguous
  , y = (t.r).y
FROM (
   VALUES
      ('(1,20,)'::foo)  -- columns need to be in default order of table
     ,('(2,50,)')       -- nothing after the last comma for NULL
   ) t (r)              -- column name for row type
WHERE  f.pkid = (t.r).pkid

Вы, очевидно, знаете имя таблицы. Если вы также знаете количество столбцов и их порядок, вы можете работать с этим.

Для каждой таблицы в PostgreSQL тип строки регистрируется автоматически. Если вы соответствуете числу столбцов в вашем выражении, вы можете привести к типу строки таблицы ('(1,50,)'::foo) тем самым назначая типы столбцов неявно. Не ставьте ничего после запятой, чтобы ввести NULL значение. Добавьте запятую для каждого неактуального конечного столбца.
На следующем шаге вы можете получить доступ к отдельным столбцам с продемонстрированным синтаксисом. Подробнее о выборе полей в руководстве.

Или вы можете добавить строку значений NULL и использовать единый синтаксис для фактических данных:

...
  VALUES
      ((NULL::foo))  -- row of NULL values
    , ('(1,20,)')    -- uniform ROW value syntax for all
    , ('(2,50,)')
...

Добавленная строка исключается WHERE пункт в вашем UPDATE,
Для других целей вы можете удалить добавленную первую строку с помощью OFFSET 1 в подзапросе.

Pro:
- Самый быстрый (по крайней мере, в моих тестах с несколькими строками и столбцами).
- Кратчайший синтаксис для нескольких строк или таблиц, где вам нужны все столбцы. - Вам не нужно прописывать столбцы таблицы - все столбцы автоматически имеют совпадающее имя.

Против:
- Не очень известный синтаксис для выбора поля из записи / строки / составного типа.
- Вам нужно знать количество и положение соответствующих столбцов в порядке по умолчанию.

4. VALUES выражение с разложенным типом строки

Как 3., но с разложенными строками в стандартном синтаксисе:

UPDATE foo f
SET    x = t.x
     , y = t.y
FROM (
   VALUES
      (('(1,20,)'::foo).*)  -- decomposed row of values
    , (2, 50, NULL)
   ) t(pkid, x, y)  -- arbitrary column names (I made them match)
WHERE  f.pkid = t.pkid;

Или снова с ведущей строкой значений NULL:

...
   VALUES
      ((NULL::foo).*)  -- row of NULL values
    , (1, 20, NULL)      -- uniform syntax for all
    , (2, 50, NULL)
...

Плюсы и минусы, как 3., но с более известным синтаксисом.
И вам нужно прописать имена столбцов (если они вам нужны).

5. VALUES типы выражений, извлеченные из типа строки

Как прокомментировал Unril, мы можем объединить достоинства 2. и 4., чтобы обеспечить только подмножество столбцов:

UPDATE foo f
SET   (  x,   y)
    = (t.x, t.y)  -- short notation, see below
FROM (
   VALUES
      ((NULL::foo).pkid, (NULL::foo).x, (NULL::foo).y)  -- subset of columns
    , (1, 20, NULL)
    , (2, 50, NULL)
   ) t(pkid, x, y)       -- arbitrary column names (I made them match)
WHERE  f.pkid = t.pkid;

Плюсы и минусы, как 4., но мы можем работать с любым подмножеством столбцов и не должны знать полный список.

Также отображается короткий синтаксис для UPDATE Само по себе это удобно для случаев с множеством столбцов. Связанные с:

• Массовое обновление всех столбцов

4. и 5. мои любимые.

Answer 2

Если у вас есть скрипт, генерирующий запрос, вы можете извлечь и кэшировать тип данных каждого столбца и создать соответствующий тип приведения. Например:

SELECT column_name,data_type,udt_name 
FROM information_schema.columns 
WHERE table_name = 'foo';

Из этого имени udt_name вы получите необходимое приведение, как вы объяснили в предыдущем абзаце. Дополнительно вы можете сделать это:

UPDATE foo
SET x = t.x
FROM (VALUES(null::int4,756),(null::int4,6300))
AS t(x,pkid)
WHERE foo.pkid = t.pkid;

Answer 3

Ваш скрипт создаст временную таблицу из foo. Он будет иметь те же типы данных, что и foo. Используйте невозможное условие, чтобы оно было пустым:

select x, y, pkid
into temp t
from foo
where pkid = -1

Сделайте ваш скрипт для вставки в него:

insert into t (x, y, pkid) values
(null, 20, 1),
(null, 50, 2)

Теперь обновите его:

update foo 
set x=t.x, y=t.y 
from t
where foo.pkid=t.pkid

Наконец бросьте это:

drop table t