Sql error 22p02 ошибка ошибочный литерал массива

I would like to execute a dynamic query using
the EXECUTE statement and put the result into a json array.

I am getting

SQL Error [22P02]: ERROR: malformed array literal: «[malformed array literal: «[{«id»: «1»}]»
Detail: «[» must introduce explicitly-specified array dimensions.
Where: PL/pgSQL function ….

Here is what I have so far.

CREATE or replace function my_function()
returns json[] as $$
declare result json[];
begin
    execute '
        SELECT array_to_json(array_agg(t)) from (
            select ....
        ) t;
    ' into result;

    -- doing some stuff with the array

    return result;
END;
$$ language 'plpgsql';

Gismo Ranas's user avatar

Gismo Ranas

6,0633 gold badges27 silver badges39 bronze badges

asked Mar 14, 2018 at 9:41

dknaack's user avatar

2

As the user «a_horse_with_no_name» stated. array_to_json does return a json object and NOT a json array.

In my case i could achieve what i want using

CREATE or replace function my_function()
returns json as $$
declare result json;
begin
    execute '
        SELECT (''{ "data": '' || json_agg(t)::text || ''}'')::json from (
            select ....
        ) t;
    ' into result;

    -- doing some stuff with the array

    return result;
END;
$$ language 'plpgsql';

answered Mar 14, 2018 at 9:57

dknaack's user avatar

dknaackdknaack

60.3k27 gold badges155 silver badges202 bronze badges

0

catalogue

Background

Problem solving


Background

When you use the go PG framework to modify a column, an error occurs.

The column to be modified is bigint [] type. Keep trying, and multiple errors are reported as follows:

Missing   “=”   after   array   dimensions

ERROR #22P02 malformed array literal

Problem solving

Modify data directly:

update table_name set  xxx= '{1,2}' where id=2;

  This modification is successful. Is it right to contact the code and convert the value types to be injected?Yes, that’s it!

Code mode: set (“column =?”, Val)   

Convert each value of Val of type [] Int64 into a string, and concatenate the string with commas: ` {% s}`

Expand to see: set (“column =?”, ` {1,2} ‘)   

Done!

Read More:

catalogue

Background

Problem solving


Background

When you use the go PG framework to modify a column, an error occurs.

The column to be modified is bigint [] type. Keep trying, and multiple errors are reported as follows:

Missing   “=”   after   array   dimensions

ERROR #22P02 malformed array literal

Problem solving

Modify data directly:

update table_name set  xxx= '{1,2}' where id=2;

  This modification is successful. Is it right to contact the code and convert the value types to be injected?Yes, that’s it!

Code mode: set (“column =?”, Val)   

Convert each value of Val of type [] Int64 into a string, and concatenate the string with commas: ` {% s}`

Expand to see: set (“column =?”, ` {1,2} ‘)   

Done!

Read More:

#json #postgresql

Вопрос:

Я хочу фильтровать целочисленный массив в postgresql, но когда я выполняю приведенный ниже запрос, он выдает мне ошибку литерального массива неправильной формы.

 select * from querytesting where 1111111111 = any((jsondoc->>'PhoneNumber')::integer[]);
 

Откройте изображение для справки —
https://i.stack.imgur.com/Py3Z2.png

Ответ №1:

any(x) хочет массив PostgreSQL как x . (jsondoc->>'PhoneNumber') , однако, дает вам текстовое представление массива JSON. Массив PostgreSQL будет выглядеть следующим образом: текст:

 '{1,2,3}'
 

но версия JSON, которую вы получите, ->> будет выглядеть так:

 '[1,2,3]'
 

Вы не можете смешивать два типа массивов.

Вместо этого вы могли бы использовать оператор JSON:

 jsondoc->'PhoneNumber' @> 1111111111::text::jsonb
 

Использование -> вместо ->> дает вам массив JSON, а не текст. Затем вы можете посмотреть, есть ли номер, который вы ищете, в этом массиве @> . Функция двойного приведения ( ::text::jsonb ) необходима для преобразования номера PostgreSQL в номер JSON для @> оператора.


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

Apologies for the late response, I briefly completely forgot about this.
This might be best suited as an echancement request, as it seems the current FDW code doesn’t do anything at all to support arrays. In any case, here are the reproduction steps.

  1. Dockerfile:
FROM supabase/postgres:13.3.0

RUN apt-get update 
    && apt-get install -y --no-install-recommends 
      build-essential 
      git 
      postgresql-server-dev-13

RUN apt-get install -y python3-pip wget
RUN python3 -m pip install -U pip
RUN python3 -m pip install wheel setuptools
RUN python3 -m pip install pytz
RUN python3 -m pip install sqlalchemy psycopg2


RUN set PATH=/usr/local/pgsql/bin:$PATH 
    && wget https://github.com/pgsql-io/multicorn2/archive/refs/tags/v2.2.tar.gz 
    && tar -xvf v2.2.tar.gz 
    && cd multicorn2-2.2 
    && make 
    && make install 

ENV POSTGRES_USER postgres 
ENV POSTGRES_PASSWORD postgres

Build and run the resulting image, exposing the port 5432 as desired, with database name, username and password all postgres.

  1. Connect to database postgres and do the following:
create database postgres_physical with owner postgres; -- this will store the physical tables

create extension multicorn;

create server alchemy_srv foreign data wrapper multicorn options (
    wrapper 'multicorn.sqlalchemyfdw.SqlAlchemyFdw'
);

create foreign table array_foreign
(arr _int4)
server alchemy_srv
options (db_url 'postgresql+psycopg2://postgres:postgres@localhost/postgres_physical', schema 'public', tablename 'array_example')

This makes the required foreign table and creates a database for the local tables.

  1. Connect to DB postgres_physical, run the following:
create table array_example (arr _int4);
insert into array_example values ('{1, 2, 3, 4, 5}')

Which creates and populates the local table.

  1. Finally, at postgres:
select * from array_foreign

Which raises the following error:
изображение
Notably, different detail text, same core issue — attempting to shove a python array into postgres syntax.

I need to run the following query in Postgres:

select left(file_date, 10) as date, lob_name, devicesegment, sum(conversion_units::numeric) as units
from bac_search.dash_search_data
where (lob_name= 'Mortgage' and file_date::date between (CURRENT_DATE - INTERVAL '30 days') and CURRENT_DATE)
      or (lob_name= 'Loans' and file_date::date between (CURRENT_DATE - INTERVAL '30 days') and CURRENT_DATE)
group by file_date, lob_name, devicesegment
order by file_date, lob_name, devicesegment;

Despite setting conversion_units to numeric, it is giving me the following error:

ERROR:  invalid input syntax for type numeric: ""
********** Error **********

ERROR: invalid input syntax for type numeric: ""
SQL state: 22P02

Of note, I’ve done some unit testing and when I run this query for Mortgage and delete the line for Loans, it works fine. I’ve isolated the problem to conversion_units::numeric for Loans. Besides the usual conversion (as I’ve specified here), I’m not sure what else to try. I read through the questions with this error, but they don’t seem to mirror my problem. Any help is appreciated! Thanks!

You need quotes around your arrays, and that’s because the array is in a text version of a row.

Easy to test by taking your input as a row and see how postgres formats it (single quotes needed around arrays here because {} is an array in text):

SELECT ROW(0,NULL,NULL, 0, 0, 0, 0, '{}', '{1,2,3,4,5}', '{1,2,3,4,5}', '{0,0.25,0.5,0.75,1}')

Returns:

(0,,,0,0,0,0,{},"{1,2,3,4,5}","{1,2,3,4,5}","{0,0.25,0.5,0.75,1}")

Therefore you need to do:

...
initcond = '(0,,,0,0,0,0,{},"{1,2,3,4,5}","{1,2,3,4,5}","{0,0.25,0.5,0.75,1}")'

Why quotes are not required on an array which is empty or has only one value:

Multiple values in an array are comma-delimited, and fields within a row are also comma-delimited. If you supply a row as '(0,{1,2})', PG will interpret this as three fields: 0, {1, 2}. Naturally in that case you’ll get an error about a malformed array. Putting a field in quotes means everything within those quotes is one field. Therefore '(0,"{1,2}")' will be interpreted correctly as 0, {1,2}. If the array is empty or contains only one value, there will be no comma, so there is no problem parsing that field correctly.

You need quotes around your arrays, and that’s because the array is in a text version of a row.

Easy to test by taking your input as a row and see how postgres formats it (single quotes needed around arrays here because {} is an array in text):

SELECT ROW(0,NULL,NULL, 0, 0, 0, 0, '{}', '{1,2,3,4,5}', '{1,2,3,4,5}', '{0,0.25,0.5,0.75,1}')

Returns:

(0,,,0,0,0,0,{},"{1,2,3,4,5}","{1,2,3,4,5}","{0,0.25,0.5,0.75,1}")

Therefore you need to do:

...
initcond = '(0,,,0,0,0,0,{},"{1,2,3,4,5}","{1,2,3,4,5}","{0,0.25,0.5,0.75,1}")'

Why quotes are not required on an array which is empty or has only one value:

Multiple values in an array are comma-delimited, and fields within a row are also comma-delimited. If you supply a row as '(0,{1,2})', PG will interpret this as three fields: 0, {1, 2}. Naturally in that case you’ll get an error about a malformed array. Putting a field in quotes means everything within those quotes is one field. Therefore '(0,"{1,2}")' will be interpreted correctly as 0, {1,2}. If the array is empty or contains only one value, there will be no comma, so there is no problem parsing that field correctly.

Using CommandBuilder there are problems with arrays type:
22P02: malformed array literal: «»System.String[]»»

Table:

CREATE TABLE Test (
Cod varchar(5) NOT NULL,
Vettore character varying(20)[],
CONSTRAINT PK_test_Cod PRIMARY KEY (Cod)
);

Code:

        Dim daDataAdapter As New Npgsql.NpgsqlDataAdapter("SELECT cod, vettore FROM test ORDER By cod", DirectCast(cntDb.InternalConnection, Npgsql.NpgsqlConnection))
        Dim cbCommandBuilder As New Npgsql.NpgsqlCommandBuilder(daDataAdapter)
        Dim dtTable As New DataTable

        cbCommandBuilder.SetAllValues = True

        With daDataAdapter
            .UpdateCommand = cbCommandBuilder.GetUpdateCommand
        End With

        daDataAdapter.Fill(dtTable)
        dtTable.Rows(0).Item("vettore") = New String() {"aaa", "bbb"}

        Try
            daDataAdapter.Update(dtTable)
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try

Exception message:
Npgsql.PostgresException (0x80004005): 22P02: malformed array literal: «System.String[]»
in System.Data.Common.DbDataAdapter.UpdatedRowStatusErrors(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
in System.Data.Common.DbDataAdapter.UpdatedRowStatus(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
in System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping)
in System.Data.Common.DbDataAdapter.UpdateFromDataTable(DataTable dataTable, DataTableMapping tableMapping)
in System.Data.Common.DbDataAdapter.Update(DataTable dataTable)

Npgsql version: 4.0.9
PostgreSQL version: 11.6

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.

#json #postgresql

Вопрос:

Я хочу фильтровать целочисленный массив в postgresql, но когда я выполняю приведенный ниже запрос, он выдает мне ошибку литерального массива неправильной формы.

 select * from querytesting where 1111111111 = any((jsondoc->>'PhoneNumber')::integer[]);
 

Откройте изображение для справки —
https://i.stack.imgur.com/Py3Z2.png

Ответ №1:

any(x) хочет массив PostgreSQL как x . (jsondoc->>'PhoneNumber') , однако, дает вам текстовое представление массива JSON. Массив PostgreSQL будет выглядеть следующим образом: текст:

 '{1,2,3}'
 

но версия JSON, которую вы получите, ->> будет выглядеть так:

 '[1,2,3]'
 

Вы не можете смешивать два типа массивов.

Вместо этого вы могли бы использовать оператор JSON:

 jsondoc->'PhoneNumber' @> 1111111111::text::jsonb
 

Использование -> вместо ->> дает вам массив JSON, а не текст. Затем вы можете посмотреть, есть ли номер, который вы ищете, в этом массиве @> . Функция двойного приведения ( ::text::jsonb ) необходима для преобразования номера PostgreSQL в номер JSON для @> оператора.


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

Human beings are born to make mistakes. Eventually, when you do some code, you also make mistakes that lead you to some errors, i.e., logical, syntax, and technical. Just like any language, a database also comes up with many errors. PostgreSQL database is full of such errors that we get daily. One of those errors is “Malformed Array Literal”. The causes of this error in the PostgreSQL database can be many. We just need to find out all those causes and remove the error. Today, we have decided to cover this article for our users who are unknown to the postgresql database error: malformed array literal. Let’s see how we can encounter and solve it within the PostgreSQL pgAmdin graphical user interface.

Let’s start with the launch of your installed PostgreSQL database by searching it through the search bar of the Windows 10 desktop front screen. On the search bar of your Windows 10 desktop( from the left bottom corner), write “pgAdmin”. The pop-up for application “pgAdmin 4” of the PostgreSQL database will be shown. You have to click on it to open it on your system. It will use 20 to 30 seconds to open itself. On opening, it will show you the dialog box to enter your password for the database server. You have to write the password that you have entered when installing the PostgreSQL database. After adding the database server password, the server is ready for our use. Within the Servers option at the left area of PostgreSQL, expand the databases. Choose the database of your choice to start working on it. We have chosen the database “aqsayasin” from our database server. Now, open the chosen database “query tool” by clicking on the icon of “query tool” from the top taskbar. It will open up the query area to do some tasks through commands in the database.

Example 01:

The very first and most-occurred cause of an error: malformed array literal in PostgreSQL database is to copy the contents of JSON-type column to some array type. Let’s make the situation something like this and resolve it after that. We need a table with a JSON type column to use JSON data. Thus, we have created a new table named “Malformed” in the database “aqsayasin” using the CREATE TABLE command. This table has been created with three different columns. Its first column, “ID” is a simple integer type, and the second column “name” is of text array type. The last column, “info” has been initialized as a “jsonb” data type to store the JSON data in it. Tap on the postgreSQL database “run” button from its taskbar. You will see that the empty table “Malformed” will be created as per the success query output beneath.

Let’s insert some records in the ID and info column of table “Malformed” casting off the INSERT INTO instruction on the query tool. We are not inserting records in the array type column “name”, because we will copy the records of jsonb column “info” to it later. Thus, we have added the JSON data into the “info” column and integer value into the “ID” column. It was quite easy to use the “VALUES” keyword and was successful as per the below output.

To get the malformed array literal error, we must use the wrong query format in the query tool. Thus, we have been using the UPDATE instruction to modify the records of the table “Malformed”. We are using the “SET” keyword to cast the array record “name” as text from the info column to the “name” column, which is empty right now. On running this instruction, we have found that this way of copying JSON data to an array-type column is throwing an error “malformed array literal”. We have to change the format of copying the data so far.

To copy the JSONB column data to some array-type column, we need to utilize the concat function within our UPDATE command. Therefore, we used the UPDATE command to modify the table “Malformed”. The SET keyword assigns the record to column “name” of array type. While assigning, it uses concat and translates function. The translate function will convert the JSON data to array type for the column “info”. After that, the concat function will add up the translated data to one in the form of an array so that it can be saved to the column “name”. The error has been removed on execution, and data has been copied properly.

Let’s display the table “Malformed” data on our pgAdmin GUI screen using the “SELECT” instruction shown below. You can see that the JSON data from column “info” is successfully copied to the array column “name”.

Example 02:

Another way to get this error on your database is using the wrong way to merge two arrays. Thus, we will be using the SELECT ARRAY query to merge the array values 11 and 25 within square brackets to a value in single inverted commas, i.e., 78 separated by the “||” sign beneath the column “Array”. The execution of this query leads to the same errors.

To resolve this error, you need to add the value after “||” into curly brackets within the single inverted commas as ’{78}’. On execution, you will see that the array will be formed as “{11,25,78}” beneath the column “Array”.

Let’s take another illustration to get the error: malformed array literal. Thus, we have been merging the array in a square bracket with the none, i.e., empty value in single commas. On running this instruction, we have found the same malformed array literal error on the output.

To recover our system from this error, we will be replacing the empty inverted commas with the “NULL” keyword in the below-shown image. On execution of this instruction, we have got the array {11,25}’ beneath the column “Array” in the output area.

Example 03:

Let’s take the last example to get the error: malformed array literal and solve it through. Assume you have a table named “Ftest” in your database with some records in it. Fetch all its records with the SELECT instruction shown below. It is fine when you are fetching all its records without any condition as per the instruction below used within the query tool.

Let’s fetch all the records of this table from ID 1 to 4 using the WHERE clause condition. The IDs have been mentioned in the simple brackets within single inverted commas. But, it leads us to a malformed array literal error.

To resolve this error, we need to combine two conditions through AND operator within the WHERE clause of the SELECT instruction. This time, our query worked greatly and displayed the records from ID 3 to 5.

Conclusion:

Finally! We have completed the explanation of solving the PostgreSQL error “malformed array literal”. We have discussed three of the different scenarios that may cause this error in the PostgreSQL database. We have also covered the solutions to all those scenarios that may cause this error to happen. Therefore, we know that you will find all these examples easy to understand and learn a new thing in the PostgreSQL database.

About the author

I am a self-motivated information technology professional with a passion for writing. I am a technical writer and love to write for all Linux flavors and Windows.

Понравилась статья? Поделить с друзьями:
  • Spn 97 fm1 3 код ошибки камаз
  • Spn 806 fmi 5 камаз код ошибки
  • Spn 792 fmi 5 cummins камаз ошибка
  • Spore mod api ошибка
  • Spore complete edition ошибка 2000