PostgreSQL: вставлять данные в таблицу из json

Теперь я использую для ручного анализа json в строку вставки так:

insert into Table (field1, field2) values (val1, val2)

но его неудобный способ вставить данные из json! Я нашел функцию json_populate_record и попытался ее использовать:

create table test (id serial, name varchar(50));
insert into test select * from json_populate_record(NULL::test, '{"name": "John"}');

но он не работает с сообщением: значение null в столбце "id" нарушает непустое ограничение PG знает, что идентификатор серийный, но притворяется глупым. То же самое делает для всех фидов со значениями по умолчанию.

Есть ли более элегантный vay для вставки данных из json в таблицу?

Ответ 1

Нет никакого простого способа вернуть json_populate_record маркер, который означает "сгенерировать это значение".

PostgreSQL не позволяет вставлять NULL, чтобы указать, что значение должно быть сгенерировано. Если вы попросите NULL Pg, значит, будет означать NULL и не хочет вас угадывать. Кроме того, вполне нормально иметь сгенерированный столбец, который не имеет ограничения NOT NULL, и в этом случае отлично вставить в него NULL.

Если вы хотите, чтобы PostgreSQL использовал таблицу по умолчанию для значения, есть два способа сделать это:

Опустить эту строку из списка столбцов INSERT; или
Явно писать DEFAULT, который действителен только в выражении VALUES

Поскольку вы не можете использовать VALUES(DEFAULT, ...) здесь, ваш единственный вариант - опустить столбец из списка столбцов INSERT:

regress=# create table test (id serial primary key, name varchar(50));
CREATE TABLE
regress=# insert into test(name) select name from json_populate_record(NULL::test, '{"name": "John"}');
INSERT 0 1

Да, это означает, что вы должны перечислить столбцы. Дважды, фактически, один раз в списке SELECT и один раз в столбце INSERT.

Чтобы избежать необходимости в этом, PostgreSQL должен иметь способ указать DEFAULT как значение для записи, поэтому json_populate_record может возвращать DEFAULT вместо NULL для столбцов, которые не определены, Возможно, это не то, что вы планировали для всех столбцов, и привело бы к вопросу о том, как DEFAULT будет обрабатываться, когда json_populate_record не используется в выражении INSERT.

Итак, я думаю, json_populate_record может оказаться менее полезным, чем вы надеялись на строки с сгенерированными ключами.

Ответ 2

Продолжая ответ Крэйга, вам, вероятно, нужно написать какую-то хранимую процедуру для выполнения необходимого динамического SQL, например:

CREATE OR REPLACE FUNCTION jsoninsert(relname text, reljson text)
  RETURNS record AS
$BODY$DECLARE
 ret RECORD;
 inputstring text;
BEGIN
  SELECT string_agg(quote_ident(key),',') INTO inputstring
    FROM json_object_keys(reljson::json) AS X (key);
  EXECUTE 'INSERT INTO '|| quote_ident(relname) 
    || '(' || inputstring || ') SELECT ' ||  inputstring 
    || ' FROM json_populate_record( NULL::' || quote_ident(relname) || ' , json_in($1)) RETURNING *'
    INTO ret USING reljson::cstring;
  RETURN ret;
END;
$BODY$
LANGUAGE plpgsql VOLATILE;

Что вы затем вызываете с помощью

SELECT jsoninsert('test', '{"name": "John"}');