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

http://sqlfiddle.com/#!17/d8122/33

json array is not self castable to postgres array. You need to either properly parse and cast it (json_array_elements, unnest, array_agg), or use some monkey hack, like:

UPDATE survey_results 
SET areas = concat('{',translate(raw#>>'{areas}','"',$$'$$),'}')::text[];

above I «prepare» json array, by changing quotes, so they would be literal, not identifier ones and prepare it to postgres array text representation '{}'

as alternative smth like:

with a as (
  select jsonb_array_elements_text(raw#>'{areas}') e from survey_results 
)
, b as (
select array_agg(e) ag from a
)
UPDATE survey_results 
SET areas = ag::text[]
FROM b;
select * from survey_results
  ;

can be used — for safer «casting»

Люди рождены, чтобы совершать ошибки. В конце концов, когда вы делаете какой-то код, вы также делаете ошибки, которые приводят к некоторым ошибкам, то есть логическим, синтаксическим и техническим. Как и в любом языке, в базе данных возникает множество ошибок. База данных PostgreSQL полна таких ошибок, которые мы получаем ежедневно. Одной из таких ошибок является «Искаженный литерал массива». Причин этой ошибки в базе данных PostgreSQL может быть много. Нам просто нужно выяснить все эти причины и устранить ошибку. Сегодня мы решили рассказать об этой статье нашим пользователям, которым неизвестна ошибка базы данных postgresql: искаженный литерал массива. Давайте посмотрим, как мы можем найти и решить эту проблему в графическом пользовательском интерфейсе PostgreSQL pgAmdin.

Давайте начнем с запуска вашей установленной базы данных PostgreSQL, выполнив поиск в строке поиска на переднем экране рабочего стола Windows 10. В строке поиска на рабочем столе Windows 10 (в левом нижнем углу) напишите «pgAdmin». Появится всплывающее окно для приложения «pgAdmin 4» базы данных PostgreSQL. Вы должны нажать на него, чтобы открыть его в вашей системе. Он будет использовать от 20 до 30 секунд, чтобы открыть себя. При открытии появится диалоговое окно для ввода пароля для сервера базы данных. Вы должны написать пароль, который вы ввели при установке базы данных PostgreSQL. После добавления пароля сервера базы данных сервер готов к использованию. В опции «Серверы» в левой части PostgreSQL разверните базы данных. Выберите базу данных по вашему выбору, чтобы начать работу над ней. Мы выбрали базу данных «aqsayasin» с нашего сервера базы данных. Теперь откройте выбранную базу данных «инструмент запроса», щелкнув значок «инструмент запроса» на верхней панели задач. Это откроет область запросов для выполнения некоторых задач с помощью команд в базе данных.

Пример 01:

Самая первая и наиболее часто встречающаяся причина ошибки: искаженный литерал массива в базе данных PostgreSQL заключается в копировании содержимого столбца типа JSON в какой-либо тип массива. Давайте сделаем ситуацию примерно такой и разрешим ее после этого. Нам нужна таблица со столбцом типа JSON для использования данных JSON. Таким образом, мы создали новую таблицу с именем «Malformed» в базе данных «aqsayasin» с помощью команды CREATE TABLE. Эта таблица была создана с тремя разными столбцами. Его первый столбец «ID» представляет собой простой целочисленный тип, а второй столбец «name» имеет тип текстового массива. Последний столбец «info» был инициализирован как тип данных «jsonb» для хранения в нем данных JSON. Нажмите кнопку запуска базы данных postgreSQL на панели задач. Вы увидите, что пустая таблица «Malformed» будет создана в соответствии с выходными данными успешного запроса ниже.

Давайте вставим несколько записей в столбец ID и информации таблицы «Malformed», отбросив инструкцию INSERT INTO в инструменте запросов. Мы не вставляем записи в столбец типа массива «имя», потому что позже мы скопируем в него записи столбца jsonb «информация». Таким образом, мы добавили данные JSON в столбец «info» и целочисленное значение в столбец «ID». Было довольно легко использовать ключевое слово «VALUES», и результат был успешным, как показано ниже.

Чтобы получить литеральную ошибку искаженного массива, мы должны использовать неправильный формат запроса в инструменте запросов. Таким образом, мы использовали инструкцию UPDATE для изменения записей таблицы «Malformed». Мы используем ключевое слово «SET», чтобы преобразовать запись массива «имя» в виде текста из информационного столбца в столбец «имя», который сейчас пуст. При выполнении этой инструкции мы обнаружили, что этот способ копирования данных JSON в столбец типа массива выдает ошибку «неверный формат литерала массива». Нам пока приходится менять формат копирования данных.

Чтобы скопировать данные столбца JSONB в какой-либо столбец типа массива, нам нужно использовать функцию concat в нашей команде UPDATE. Поэтому мы использовали команду UPDATE для изменения таблицы «Malformed». Ключевое слово SET присваивает запись столбцу «имя» типа массива. При назначении он использует concat и функцию перевода. Функция перевода преобразует данные JSON в тип массива для столбца «информация». После этого функция concat сложит переведенные данные в единицу в виде массива, чтобы их можно было сохранить в столбец «имя». Ошибка была устранена при выполнении, и данные были скопированы правильно.

Давайте отобразим данные таблицы «Искаженные» на нашем экране графического интерфейса pgAdmin, используя инструкцию «SELECT», показанную ниже. Вы можете видеть, что данные JSON из столбца «информация» успешно скопированы в столбец массива «имя».

Пример 02:

Другой способ получить эту ошибку в вашей базе данных — использовать неправильный способ объединения двух массивов. Таким образом, мы будем использовать запрос SELECT ARRAY для объединения значений массива 11 и 25 в пределах квадрата. скобки до значения в одинарных кавычках, т. е. 78, разделенных «||» подпись под колонкой «Множество». Выполнение этого запроса приводит к тем же ошибкам.

Чтобы устранить эту ошибку, вам нужно добавить значение после «||» в фигурные скобки в одинарных кавычках как ‘{78}’. При выполнении вы увидите, что массив будет сформирован как «{11,25,78}» под столбцом «Массив».

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

Чтобы восстановить нашу систему от этой ошибки, мы заменим пустые кавычки ключевым словом «NULL» на изображении ниже. При выполнении этой инструкции мы получили массив {11,25}’ под столбцом «Массив» в области вывода.

Пример 03:

Давайте возьмем последний пример, чтобы получить ошибку: искаженный литерал массива и решить ее. Предположим, у вас есть таблица с именем «Ftest» в вашей базе данных с некоторыми записями в ней. Извлеките все его записи с помощью инструкции SELECT, показанной ниже. Это нормально, когда вы извлекаете все его записи без каких-либо условий в соответствии с приведенной ниже инструкцией, используемой в инструменте запросов.

Давайте выберем все записи этой таблицы с идентификаторами от 1 до 4, используя условие предложения WHERE. Идентификаторы указаны в простых скобках в одинарных кавычках. Но это приводит нас к неправильно сформированной литеральной ошибке массива.

Чтобы устранить эту ошибку, нам нужно объединить два условия с помощью оператора AND в предложении WHERE инструкции SELECT. На этот раз наш запрос работал отлично и отображал записи с ID 3 по 5.

Заключение:

Ну наконец то! Мы завершили объяснение решения ошибки PostgreSQL «неверный формат массива». Мы обсудили три различных сценария, которые могут вызвать эту ошибку в базе данных PostgreSQL. Мы также рассмотрели решения для всех тех сценариев, которые могут вызвать эту ошибку. Поэтому мы знаем, что вы найдете все эти примеры простыми для понимания и узнаете что-то новое в базе данных PostgreSQL.

Just a slight variation to Chris’s answer:

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);

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:

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);

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 is a simplified version of my table:

class Alert(Base):
    __tablename__ = 'alert'

    id = Column(Integer, primary_key=True)
    created_on = Column(DateTime(timezone=True))
    category = Column(Unicode, nullable=False)
    parameters = Column(ARRAY(Unicode), nullable=True)

When trying to insert the following multidimensional array into the table, I get a DataError telling me that my array is malformed.

DataError: (DataError) malformed array literal: "numbers"
LINE 1: ...RRAY[ARRAY['key', 'sXl5SoNh0KY'], ARRAY['nu...
                                                   ^
DETAIL:  Array value must start with "{" or dimension information.
'INSERT INTO alert (created_on, category, parameters) 
VALUES (%(created_on)s, %(category)s, %(parameters)s) 
RETURNING alert.id' {
'created_on': datetime.datetime(2015, 6, 24, 1, 52, 30, 631330, tzinfo=<UTC>)
'category': u'new_cases',
'parameters':
    [[u'key', u'sXl5SoNh0KY'],
    ['numbers', [u'8129431', u'8669290', u'8754131', u'8871813', u'8927606']],
    ['status', 'all']]
}

I was under the impression that Postgres’ ARRAY type is multidimensional regardless of how it is created (http://thread.gmane.org/gmane.comp.python.sqlalchemy.user/31186), so why is this error occurring?