Автоматически приводить тип значения к типу столбца

TL;ДР; Короче говоря, мне нужно каким-то образом преобразовать text в unknown, откуда Postgres волшебным образом приведет это к правильному типу; или какое-то альтернативное решение этого, помня о вещах, которых я хочу избежать.

Ошибка в вопросе:

ERROR: column "id" is of type integer but expression is of type text

Скажем, у меня есть эта таблица:

CREATE TEMP TABLE unknown_test (
  id int,
  some_timestamp timestamp,
  value1 int,
  value2 int,
  value3 text);

В настоящее время я делаю DML для этой таблицы с такими запросами:

INSERT INTO unknown_test (id, some_timestamp, value1, value2, value3)
VALUES ('5', '2018-01-10 14:11:03.763396', '3', '15', 'test2');

Таким образом, значения имеют тип unknown, и в Postgres для этого есть какое-то встроенное приведение (его нет в select * from pg_cast where castsource = 'unknown'::regtype;). Это работает, но несколько медленно.

Что я хочу сделать, так это (очевидно, у меня есть настоящая таблица, а не values()):

INSERT INTO unknown_test (id, some_timestamp, value1, value2, value3)
SELECT json_data->>'id', json_data->>'some_timestamp', json_data->>'value1', json_data->>'value2', json_data->>'value3'
  FROM (VALUES (jsonb_build_object('id', 1, 'some_timestamp', now(), 'value1', 21, 'value2', 5, 'value3', 'test')),
               (jsonb_build_object('id', 2, 'some_timestamp', now(), 'value1', 22, 'value2', 15, 'value3', 'test2')),
               (jsonb_build_object('id', 3, 'some_timestamp', now(), 'value1', 32, 'value2', 25, 'value3', 'test5')),
               (jsonb_build_object('id', 4, 'some_timestamp', now(), 'value1', 42, 'value2', 55, 'value3', 'test7'))
       ) AS j(json_data);

К сожалению, они дадут тип text и будут жаловаться, что мне нужно явно указать его. Я не могу этого сделать, потому что я не знаю, что это за типы. Я мог бы узнать, конечно, проверив pg_catalog или сохранив в данных json информацию о типе. Оба они требуют некоторых дополнительных вычислений и/или хранения, и я хочу избежать ненужных накладных расходов (мой pg_catalog действительно толстый).

Второе, чего я хочу избежать, — это CREATE CAST для текстового типа, если кто-то не заверит меня, что это ничего не сломает.

Выполнение цикла и динамического SQL для получения типа unknown - это мой текущий подход, и мне нужно что-то более быстрое, поэтому моя идея заключалась в том, чтобы вместо этого использовать цикл, а таблицу.


postgresql postgresql-9.6
person Łukasz Kamiński    schedule 10.01.2018    source источник

Ответы (1)


arrow_upward
2
arrow_downward

Вы можете использовать jsonb_populate_record для этого:

SELECT (jsonb_populate_record(null::unknown_test, json_data)).*
FROM ...

Это создаст запись того же типа, что и таблица unknown_test, а затем вся запись будет расширена на отдельные столбцы с использованием синтаксиса (...).*.

Для этого необходимо, чтобы ключи (первого уровня) в документе JSON имели в точности имена столбцов в таблице.

person a_horse_with_no_name    schedule 10.01.2018