logo
Tags down

shadow

facing 'malformed array literal' when trying to insert json in postgres


By : Eduardo Nazato
Date : October 16 2020, 08:10 AM
wish of those help You must extract the JSON array element as a text and convert it to a VARCHAR array before inserting.
code :
CREATE OR REPLACE FUNCTION data(job_req JSONB)
  RETURNS VOID
AS $$
BEGIN
INSERT INTO "JobRequirements" (expertise,"phoneNumbers", "location")
  SELECT 
    job_req ->> 'expertise' ,
     array_agg(d.phonenumber::VARCHAR ) ,
    job_req ->> 'location' 
    FROM jsonb_array_elements_text(job_req->'phoneNumbers') as d(phonenumber);
END;
$$ LANGUAGE plpgsql;


Share : facebook icon twitter icon

DataError: Malformed array literal when inserting multidimensional array into PostgreSQL


By : user3849019
Date : March 29 2020, 07:55 AM
To fix this issue Arrays in PostgreSQL can be multidimensional, but all the elements must have the same number of dimensions (http://www.postgresql.org/docs/9.4/interactive/arrays.html), so you can write:
code :
SELECT ARRAY[ARRAY[1,2], ARRAY[3,4]];

=> "{{1,2},{3,4}}"
SELECT ARRAY[ARRAY[1,2], ARRAY[3]]

ERROR:  multidimensional arrays must have array expressions with matching dimensions

Rails. Filter Postgres objects by their Arrays. ERROR: malformed array literal


By : Z.Andy
Date : March 29 2020, 07:55 AM
wish of those help ActiveRecord does not automatically create queries for array type columns.
Querying an array column can be done with:
code :
Profile.where("items@> ARRAY[?]::varchar[]", ["0", "1"])
# or 
Profile.where("items@> ARRAY[?]::integer[]", [0, 1])

malformed array literal - PostgreSQL


By : Alex Stephan
Date : March 29 2020, 07:55 AM
I think the issue was by ths following , 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:
code :
UPDATE survey_results 
SET areas = concat('{',translate(raw#>>'{areas}','"',$$'$$),'}')::text[];
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
  ;

Postgresql insert multiple columns malformed array literal


By : helst_luzhi
Date : March 29 2020, 07:55 AM
hop of those help? The error message means that the columns licensenum and businessowner (or one of them) are arrays. Probably they should be simple text, then your program would work well. However, if you really want them to be arrays, then you should pass lists, not strings as arguments, e.g.:
code :
cur.executemany(
    "insert into public.\"Texas\"(licensenum,businessowner) VALUES (%s,%s)", 
    [(['value1'],['value2']),(['value3'],['value4'])])

node & postgres - error: malformed array literal: timestamp


By : Leo Knudsen
Date : March 29 2020, 07:55 AM
seems to work fine node-postgres appears to only accept dates as actual Date objects, not as strings. Documentation
code :
const queryInsert = {
  text: 'INSERT INTO my_table(some_date, last_update) VALUES($1, $2)',
  values: [new Date("2020-01-15T20:09:22.711+02:00"), new Date()]
}
shadow
Privacy Policy - Terms - Contact Us © voile276.org