Ошибка ошибочный литерал массива

«ERROR:malformed array literal» when using json_to_record with a JSON array element in Postgres 9.4

This illustrates the issue nicely:

When column b is of type text, and not an array, the following works:

But if I define the b column as an array, I get this error:

How can I convince/coerce json_to_record (or json_populate_record ) to convert a JSON array into the Postgres array of the target column type?

2 Answers 2

Just a slight variation to Chris’s answer:

The idea is the same: massage the JSON array into an array — in this case, through an array literal. In addition to a bit cleaner looking code (though I love it, regex usually does not help much in this regard :), it seems slighly faster, too:

On this dataset and on my test box, the regex version shows and average execution time of 300 ms, while my version shows 210 ms.

This may not be the most elegant solution, but it will fix your issues.

It’s pretty straightforward how it works:

First, take the text string in b , and strip it down to the useful information. This is done by using regexp_replace() as

to remove all the instances of [ , » , ] , and any whitespace characters, or more specifically, to replace any instances of these characters with » , and to apply this globally, signalled by using the flag ‘g’ .

Next, simply split the string to an array using string_to_array() as

where in this case your_string is simply the result of the above regexp_replace() . The second argument ‘,’ indicated to string_to_array() that the items are comma-separated.

This will yield a text[] field containing your desired entries.

Источник

Error malformed array literal postgresql

PostgreSQL позволяет определять столбцы таблицы как многомерные массивы переменной длины. Элементами массивов могут быть любые встроенные или определённые пользователями базовые типы, перечисления, составные типы, типы-диапазоны или домены.

8.15.1. Объявления типов массивов

Чтобы проиллюстрировать использование массивов, мы создадим такую таблицу:

Как показано, для объявления типа массива к названию типа элементов добавляются квадратные скобки ( [] ). Показанная выше команда создаст таблицу sal_emp со столбцами типов text ( name ), одномерный массив с элементами integer ( pay_by_quarter ), представляющий квартальную зарплату работников, и двухмерный массив с элементами text ( schedule ), представляющий недельный график работника.

Команда CREATE TABLE позволяет также указать точный размер массивов, например так:

Однако текущая реализация игнорирует все указанные размеры, т. е. фактически размер массива остаётся неопределённым.

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

Для объявления одномерных массивов можно применять альтернативную запись с ключевым словом ARRAY , соответствующую стандарту SQL. Столбец pay_by_quarter можно было бы определить так:

Или без указания размера массива:

Заметьте, что и в этом случае PostgreSQL не накладывает ограничения на фактический размер массива.

8.15.2. Ввод значения массива

Чтобы записать значение массива в виде буквальной константы, заключите значения элементов в фигурные скобки и разделите их запятыми. (Если вам знаком C, вы найдёте, что это похоже на синтаксис инициализации структур в C.) Вы можете заключить значение любого элемента в двойные кавычки, а если он содержит запятые или фигурные скобки, это обязательно нужно сделать. (Подробнее это описано ниже.) Таким образом, общий формат константы массива выглядит так:

где разделитель — символ, указанный в качестве разделителя в соответствующей записи в таблице pg_type . Для стандартных типов данных, существующих в дистрибутиве PostgreSQL , разделителем является запятая ( , ), за исключением лишь типа box , в котором разделитель —точка с запятой ( ; ). Каждое значение здесь — это либо константа типа элемента массива, либо вложенный массив. Например, константа массива может быть такой:

Эта константа определяет двухмерный массив 3×3, состоящий из трёх вложенных массивов целых чисел.

Чтобы присвоить элементу массива значение NULL, достаточно просто написать NULL (регистр символов при этом не имеет значения). Если же требуется добавить в массив строку, содержащую « NULL » , это слово нужно заключить в двойные кавычки.

(Такого рода константы массивов на самом деле представляют собой всего лишь частный случай констант, описанных в Подразделе 4.1.2.7. Константа изначально воспринимается как строка и передаётся процедуре преобразования вводимого массива. При этом может потребоваться явно указать целевой тип.)

Теперь мы можем показать несколько операторов INSERT :

Результат двух предыдущих команд:

В многомерных массивов число элементов в каждой размерности должно быть одинаковым; в противном случае возникает ошибка. Например:

Также можно использовать синтаксис конструктора ARRAY :

Заметьте, что элементы массива здесь — это простые SQL-константы или выражения; и поэтому, например строки будут заключаться в одинарные апострофы, а не в двойные, как в буквальной константе массива. Более подробно конструктор ARRAY обсуждается в Подразделе 4.2.12.

8.15.3. Обращение к массивам

Добавив данные в таблицу, мы можем перейти к выборкам. Сначала мы покажем, как получить один элемент массива. Этот запрос получает имена сотрудников, зарплата которых изменилась во втором квартале:

Индексы элементов массива записываются в квадратных скобках. По умолчанию в PostgreSQL действует соглашение о нумерации элементов массива с 1, то есть в массиве из n элементов первым считается array[1] , а последним — array[ n ] .

Этот запрос выдаёт зарплату всех сотрудников в третьем квартале:

Мы также можем получать обычные прямоугольные срезы массива, то есть подмассивы. Срез массива обозначается как нижняя-граница : верхняя-граница для одной или нескольких размерностей. Например, этот запрос получает первые пункты в графике Билла в первые два дня недели:

Если одна из размерностей записана в виде среза, то есть содержит двоеточие, тогда срез распространяется на все размерности. Если при этом для размерности указывается только одно число (без двоеточия), в срез войдут элемент от 1 до заданного номера. Например, в этом примере [2] будет равнозначно [1:2] :

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

Значения нижняя-граница и/или верхняя-граница в указании среза можно опустить; опущенная граница заменяется нижним или верхним пределом индексов массива. Например:

Выражение обращения к элементу массива возвратит NULL, если сам массив или одно из выражений индексов элемента равны NULL. Значение NULL также возвращается, если индекс выходит за границы массива (это не считается ошибкой). Например, если schedule в настоящее время имеет размерности [1:3][1:2] , результатом обращения к schedule[3][3] будет NULL. Подобным образом, при обращении к элементу массива с неправильным числом индексов возвращается NULL, а не ошибка.

Аналогично, NULL возвращается при обращении к срезу массива, если сам массив или одно из выражений, определяющих индексы элементов, равны NULL. Однако в других случаях, например, когда границы среза выходят за рамки массива, возвращается не NULL, а пустой массив (с размерностью 0). (Так сложилось исторически, что в этом срезы отличаются от обращений к обычным элементам.) Если запрошенный срез пересекает границы массива, тогда возвращается не NULL, а срез, сокращённый до области пересечения.

Текущие размеры значения массива можно получить с помощью функции array_dims :

array_dims выдаёт результат типа text , что удобно скорее для людей, чем для программ. Размеры массива также можно получить с помощью функций array_upper и array_lower , которые возвращают соответственно верхнюю и нижнюю границу для указанной размерности:

array_length возвращает число элементов в указанной размерности массива:

cardinality возвращает общее число элементов массива по всем измерениям. Фактически это число строк, которое вернёт функция unnest :

8.15.4. Изменение массивов

Значение массива можно заменить полностью так:

или используя синтаксис ARRAY :

Также можно изменить один элемент массива:

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

Сохранённый массив можно расширить, определив значения ранее отсутствовавших в нём элементов. При этом все элементы, располагающиеся между существовавшими ранее и новыми, принимают значения NULL. Например, если массив myarray содержит 4 элемента, после присваивания значения элементу myarray[6] его длина будет равна 6, а myarray[5] будет содержать NULL. В настоящее время подобное расширение поддерживается только для одномерных, но не многомерных массивов.

Определяя элементы по индексам, можно создавать массивы, в которых нумерация элементов может начинаться не с 1. Например, можно присвоить значение выражению myarray[-2:7] и таким образом создать массив, в котором будут элементы с индексами от -2 до 7.

Значения массива также можно сконструировать с помощью оператора конкатенации, || :

Оператор конкатенации позволяет вставить один элемент в начало или в конец одномерного массива. Он также может принять два N -мерных массива или массивы размерностей N и N+1 .

Когда в начало или конец одномерного массива вставляется один элемент, в образованном в результате массиве будет та же нижняя граница, что и в массиве-операнде. Например:

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

Когда к массиву размерности N+1 спереди или сзади добавляется N -мерный массив, он вставляется аналогично тому, как в массив вставляется элемент (это было описано выше). Любой N -мерный массив по сути является элементом во внешней размерности массива, имеющего размерность N+1 . Например:

Массив также можно сконструировать с помощью функций array_prepend , array_append и array_cat . Первые две функции поддерживают только одномерные массивы, а array_cat поддерживает и многомерные. Несколько примеров:

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

В показанных примерах анализатор запроса видит целочисленный массив с одной стороны оператора конкатенации и константу неопределённого типа с другой. Согласно своим правилам разрешения типа констант, он полагает, что она имеет тот же тип, что и другой операнд — в данном случае целочисленный массив. Поэтому предполагается, что оператор конкатенации здесь представляет функцию array_cat , а не array_append . Если это решение оказывается неверным, его можно скорректировать, приведя константу к типу элемента массива; однако может быть лучше явно использовать функцию array_append .

8.15.5. Поиск значений в массивах

Чтобы найти значение в массиве, необходимо проверить все его элементы. Это можно сделать вручную, если вы знаете размер массива. Например:

Однако с большим массивами этот метод становится утомительным, и к тому же он не работает, когда размер массива неизвестен. Альтернативный подход описан в Разделе 9.24. Показанный выше запрос можно было переписать так:

А так можно найти в таблице строки, в которых массивы содержат только значения, равные 10000:

Кроме того, для обращения к элементам массива можно использовать функцию generate_subscripts . Например так:

Эта функция описана в Таблице 9.65.

Также искать в массиве значения можно, используя оператор && , который проверяет, перекрывается ли левый операнд с правым. Например:

Этот и другие операторы для работы с массивами описаны в Разделе 9.19. Он может быть ускорен с помощью подходящего индекса, как описано в Разделе 11.2.

Вы также можете искать определённые значения в массиве, используя функции array_position и array_positions . Первая функция возвращает позицию первого вхождения значения в массив, а вторая — массив позиций всех его вхождений. Например:

Подсказка

Массивы — это не множества; необходимость поиска определённых элементов в массиве может быть признаком неудачно сконструированной базы данных. Возможно, вместо массива лучше использовать отдельную таблицу, строки которой будут содержать данные элементов массива. Это может быть лучше и для поиска, и для работы с большим количеством элементов.

8.15.6. Синтаксис вводимых и выводимых значений массива

Внешнее текстовое представление значения массива состоит из записи элементов, интерпретируемых по правилам ввода/вывода для типа элемента массива, и оформления структуры массива. Оформление состоит из фигурных скобок ( < и >), окружающих значение массива, и знаков-разделителей между его элементами. В качестве знака-разделителя обычно используется запятая ( , ), но это может быть и другой символ; он определяется параметром typdelim для типа элемента массива. Для стандартных типов данных, существующих в дистрибутиве PostgreSQL , разделителем является запятая ( , ), за исключением лишь типа box , в котором разделитель — точка с запятой ( ; ). В многомерном массиве у каждой размерности (ряд, плоскость, куб и т. д.) есть свой уровень фигурных скобок, а соседние значения в фигурных скобках на одном уровне должны отделяться разделителями.

Функция вывода массива заключает значение элемента в кавычки, если это пустая строка или оно содержит фигурные скобки, знаки-разделители, кавычки, обратную косую черту, пробельный символ или это текст NULL . Кавычки и обратная косая черта, включённые в такие значения, преобразуются в спецпоследовательность с обратной косой чертой. Для числовых типов данных можно рассчитывать на то, что значения никогда не будут выводиться в кавычках, но для текстовых типов следует быть готовым к тому, что выводимое значение массива может содержать кавычки.

По умолчанию нижняя граница всех размерностей массива равна одному. Чтобы представить массивы с другими нижними границами, перед содержимым массива можно указать диапазоны индексов. Такое оформление массива будет содержать квадратные скобки ( [] ) вокруг нижней и верхней границ каждой размерности с двоеточием ( : ) между ними. За таким указанием размерности следует знак равно ( = ). Например:

Процедура вывода массива включает в результат явное указание размерностей, только если нижняя граница в одной или нескольких размерностях отличается от 1.

Если в качестве значения элемента задаётся NULL (в любом регистре), этот элемент считается равным непосредственно NULL. Если же оно включает кавычки или обратную косую черту, элементу присваивается текстовая строка « NULL » . Кроме того, для обратной совместимости с версиями PostgreSQL до 8.2, параметр конфигурации array_nulls можно выключить (присвоив ему off ), чтобы строки NULL не воспринимались как значения NULL.

Как было показано ранее, записывая значение массива, любой его элемент можно заключить в кавычки. Это нужно делать, если при разборе значения массива без кавычек возможна неоднозначность. Например, в кавычки необходимо заключать элементы, содержащие фигурные скобки, запятую (или разделитель, определённый для данного типа), кавычки, обратную косую черту, а также пробельные символы в начале или конце строки. Пустые строки и строки, содержащие одно слово NULL , также нужно заключать в кавычки. Чтобы включить кавычки или обратную косую черту в значение, заключённое в кавычки, добавьте обратную косую черту перед таким символом. С другой стороны, чтобы обойтись без кавычек, таким экранированием можно защитить все символы в данных, которые могут быть восприняты как часть синтаксиса массива.

Перед открывающей и после закрывающей скобки можно добавлять пробельные символы. Пробелы также могут окружать каждую отдельную строку значения. Во всех случаях такие пробельные символы игнорируются. Однако все пробелы в строках, заключённых в кавычки, или окружённые не пробельными символами, напротив, учитываются.

Подсказка

Записывать значения массивов в командах SQL часто бывает удобнее с помощью конструктора ARRAY (см. Подраздел 4.2.12). В ARRAY отдельные значения элементов записываются так же, как если бы они не были членами массива.

Источник

The solution is to assign decomposed values:

CREATE OR REPLACE FUNCTION net.get_route(beg_ int8, end_ int8, mida int8[], dist int4)
  RETURNS route_part AS
$func$
DECLARE
   routerec route_part;
BEGIN
   SELECT INTO routerec * FROM net.get_route_part_dist(beg_, end_, dist);
   RETURN routerec;
END
$func$ LANGUAGE plpgsql;

Since routerec is a row type (composite type), The columns of the SELECT list must match the columns of the row type. The form you had would attempt to fit the value (as a whole) returned by net.get_route_part_dist() into the first column of routerec.

Quoting the manual:

If a row or a variable list is used as target, the query’s result
columns must exactly match the structure of the target as to number
and data types

Postgres tries to fit your composite type (or rather its text representation) into bigint[], the first column of the composite type routerec. The error message you quoted is the consequence.

Explanation in the manual:

If the expression’s result data type doesn’t match the variable’s data
type, the value will be coerced as though by an assignment cast (see
Section 10.4). If no assignment cast is known for the pair of data
types involved, the PL/pgSQL interpreter will attempt to convert the
result value textually, that is by applying the result type’s output
function followed by the variable type’s input function. Note that
this could result in run-time errors generated by the input function,
if the string form of the result value is not acceptable to the input function.

This can be confusing and fooled the first time as well. The distinction seems necessary since INTO allows assigning a list of target variables at once.
The bottom line is this: Decompose row types with SELECT * FROM ... when assigning to a row / record / composite type with INTO. Else it will be assigned to the first target column as a whole.

Avoid these inefficient forms:
Like you commented:

SELECT INTO routerec (net.get_route_part_dist(beg_, end_, dist)).nodea
, (net.get_route_part_dist(beg_, end_, dist)).edgea
, (net.get_route_part_dist(beg_, end_, dist)).geom;

Or, less verbose, but equally inefficient:

SELECT INTO routerec (net.get_route_part_dist(beg_, end_, dist)).*;

Each would evaluate the function multiple times — as opposed to:
SELECT INTO routerec * FROM net.get_route_part_dist(beg_, end_, dist)

Related:

• Use of custom return types in a FOR loop in plpgsql
• Passing array of a composite type to stored procedure

Simple alternative

The simple alternative for your simple case: direct assignment (without INTO):

  routerec := net.get_route_part_dist(beg_, end_, dist);
  RETURN routerec;

Simple assignment only allows a single target to begin with.
Or return the result directly:

  RETURN net.get_route_part_dist(beg_, end_, dist);

The solution is to assign decomposed values:

CREATE OR REPLACE FUNCTION net.get_route(beg_ int8, end_ int8, mida int8[], dist int4)
  RETURNS route_part AS
$func$
DECLARE
   routerec route_part;
BEGIN
   SELECT INTO routerec * FROM net.get_route_part_dist(beg_, end_, dist);
   RETURN routerec;
END
$func$ LANGUAGE plpgsql;

Since routerec is a row type (composite type), The columns of the SELECT list must match the columns of the row type. The form you had would attempt to fit the value (as a whole) returned by net.get_route_part_dist() into the first column of routerec.

Quoting the manual:

If a row or a variable list is used as target, the query’s result
columns must exactly match the structure of the target as to number
and data types

Postgres tries to fit your composite type (or rather its text representation) into bigint[], the first column of the composite type routerec. The error message you quoted is the consequence.

Explanation in the manual:

If the expression’s result data type doesn’t match the variable’s data
type, the value will be coerced as though by an assignment cast (see
Section 10.4). If no assignment cast is known for the pair of data
types involved, the PL/pgSQL interpreter will attempt to convert the
result value textually, that is by applying the result type’s output
function followed by the variable type’s input function. Note that
this could result in run-time errors generated by the input function,
if the string form of the result value is not acceptable to the input function.

This can be confusing and fooled the first time as well. The distinction seems necessary since INTO allows assigning a list of target variables at once.
The bottom line is this: Decompose row types with SELECT * FROM ... when assigning to a row / record / composite type with INTO. Else it will be assigned to the first target column as a whole.

Avoid these inefficient forms:
Like you commented:

SELECT INTO routerec (net.get_route_part_dist(beg_, end_, dist)).nodea
, (net.get_route_part_dist(beg_, end_, dist)).edgea
, (net.get_route_part_dist(beg_, end_, dist)).geom;

Or, less verbose, but equally inefficient:

SELECT INTO routerec (net.get_route_part_dist(beg_, end_, dist)).*;

Each would evaluate the function multiple times — as opposed to:
SELECT INTO routerec * FROM net.get_route_part_dist(beg_, end_, dist)

Related:

• Use of custom return types in a FOR loop in plpgsql
• Passing array of a composite type to stored procedure

Simple alternative

The simple alternative for your simple case: direct assignment (without INTO):

  routerec := net.get_route_part_dist(beg_, end_, dist);
  RETURN routerec;

Simple assignment only allows a single target to begin with.
Or return the result directly:

  RETURN net.get_route_part_dist(beg_, end_, dist);

select * 
from json_to_record('{"a":1,"b":["hello", "There"],"c":"bar"}') 
    as x(a int, b text, d text);

 a |         b          | d
---+--------------------+---
 1 | ["hello", "There"] |

select * 
from json_to_record('{"a":1,"b":["hello", "There"],"c":"bar"}') 
    as x(a int, b text[], d text)

ERROR:  malformed array literal: "["hello", "There"]"
DETAIL:  "[" must introduce explicitly-specified array dimensions.

Ответы:

SELECT a, translate(b, '[]', '{}')::text[] AS b, d
FROM json_to_record('{"a": 1, "b": ["hello", "There"], "c": "bar"}')
AS x(a int, b text, d text);

CREATE TABLE jsonb_test (
    id serial,
    data jsonb
);

INSERT INTO jsonb_test (id, data)
SELECT i, format('{"a": %s, "b": ["foo", "bar"], "c": "baz"}', i::text)::jsonb 
FROM generate_series(1,10000) t(i);

SELECT a, string_to_array(regexp_replace(b, '[*"*s*]*','','g'),',') AS b, d
FROM jsonb_test AS j, 
LATERAL json_to_record(j.data::json) AS r(a int, b text, d text);

-- versus 

SELECT a, translate(b, '[]', '{}')::text[] AS b, d
FROM jsonb_test AS j, 
LATERAL json_to_record(j.data::json) AS r(a int, b text, d text);

SELECT a,string_to_array(regexp_replace(b, '[*"*s*]*','','g'),',') AS b,d
FROM json_to_record('{"a":1,"b":["hello", "There"],"c":"bar"}')
AS x(a int, b text, d text);

regexp_replace(b, '[*"*s*]*','','g')

string_to_array(your_string,',')