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

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.