Postgresql – Postgres 12: Inserting array of composite types, composite type has an array field. Malformed array literal, unexpected end of input

arraypostgresqlstored-procedures

I'm having an issue with inserting a simple array of integers into my table in Postgres via a procedure. The array of integers is declared within a composite type and I'm trying to insert an array of these types via the procedure.

Just to put this out there: this code works without the array of numbers (i.e. if I were to just remove the variable as_ids from the table, composite type, and procedure, the code runs the way its supposed to. for some reason it just doesn't work with the array).

The table

CREATE TABLE user_ans(
ans_id INT GENERATED ALWAYS AS IDENTITY,
fq_id INT NOT NULL,
p_id INT NOT NULL,
j_id INT NOT NULL,
u_id INT NOT NULL,
d_id INT,
ans TEXT NOT NULL,
as_ids INT[] NOT NULL,
created TIMESTAMP,
updated TIMESTAMP
);

The composite type

CREATE TYPE new_ans_obj AS (
    ans TEXT, 
    as_ids INT[],
    created TIMESTAMP,
    d_id INT,
    fq_id INT,
    j_id INT,
    p_id INT,
    u_id INT
);

The procedure

CREATE OR REPLACE PROCEDURE add_ans_arr (
    new_ans new_ans_obj[]
)
    LANGUAGE plpgsql
AS $$
BEGIN
    INSERT INTO user_ans(ans, as_ids, created, d_id, fq_id, j_id, p_id, u_id)
    SELECT * from unnest(new_ans);
    COMMIT;
END;$$;

Attempts

Command I use:

call add_ans_arr((ARRAY['(sdf,{2002,2005},2021-01-19T21:48:28.997Z,1001,7001,3,6001,1)', '(sdf,{2002,2005},2021-01-19T21:48:29.626Z,1001,7002,3,6001,1)'])::new_ans_obj[]);

Output:

ERROR:  malformed array literal: "{2002"
LINE 1: call add_ans_arr((ARRAY['(sdf,{2002,2005},2021-01-19T21:4...
                                   ^
DETAIL:  Unexpected end of input.

Command I use:

call add_ans_arr((ARRAY['(sdf,(ARRAY[2002,2005])::int[],2021-01-19T21:48:28.997Z,1001,7001,3,6001,1)', '(sdf,(ARRAY[2002,2005])::int[],2021-01-19T21:48:29.626Z,1001,7002,3,6001,1)'])::new_ans_obj[]);

Output:

ERROR:  malformed array literal: "(ARRAY[2002"
LINE 1: call add_ans_arr((ARRAY['(sdf,(ARRAY[2002,2005])::int[],2021...
                                ^
DETAIL:  Array value must start with "{" or dimension information.

Post obvious attempts

I've also tried different variations of the two above commands (using parenthesis, putting the size of the array in, etc), but with similar outputs. I've read other posts about inserting arrays into Postgres, as well as the documentation and it seems like I'm using the right syntax (although probably not)?

I was thinking that the array of integers might be conflicting with the 'unnest' function, so I rewrote the procedure to following, but still the same problem pops up:

CREATE OR REPLACE PROCEDURE add_ans_arr (
    arr new_ans_obj[]
)
    LANGUAGE plpgsql
AS $$
DECLARE
    ans_obj new_ans_obj;
BEGIN
    FOREACH ans_obj IN ARRAY arr
    LOOP
        INSERT INTO user_ans(ans, as_ids, created, d_id, fq_id, j_id, p_id, u_id)
        VALUES (ans_obj.ans, ans_obj.as_ids, ans_obj.created, ans_obj.d_id, ans_obj.fq_id, ans_obj.j_id, ans_obj.p_id, ans_obj.u_id);
    END LOOP;
    COMMIT;
END;$$;

I'm able to individually insert these objects into the table:

insert into user_ans(ans, as_ids, created, d_id, fq_id, j_id, p_id, u_id) VALUES ('lala', '{2002}', '2021-01-19T21:48:29.626Z', 1001, 7002, 3, 6001, 1);

But I don't really understand why the above method isn't working? I've tried putting the single quotes into the my first command, but I just get a syntax error. Also I don't want to mess too much with the

ARRAY['','']::ans_obj[]

structure because it works for the rest of my other procedures, and this structure is generated by my server. Although I'm open to change it the server-side code if there's something horribly wrong with this structure, but again – it works for the rest of my stored procedures.

Any direction/help would be greatly appreciated.

Best Answer

Write the inner array in braces, and escape the comma in that with double quotes around the array literal:

SELECT ARRAY[
          '(sdf,"{2002,2005}",2021-01-19T21:48:28.997Z,1001,7001,3,6001,1)',
          '(sdf,"{2002,2005}",2021-01-19T21:48:29.626Z,1001,7002,3,6001,1)'
       ]::new_ans_obj[];

                                                                      array                                                                      
-------------------------------------------------------------------------------------------------------------------------------------------------
 {"(sdf,\"{2002,2005}\",\"2021-01-19 21:48:28.997\",1001,7001,3,6001,1)","(sdf,\"{2002,2005}\",\"2021-01-19 21:48:29.626\",1001,7002,3,6001,1)"}
(1 row)

PL/pgSQL function

If you need a plpgsql function for some undeclared reason:

CREATE OR REPLACE FUNCTION update_table_variadic(VARIADIC _books_arr books_table[])
  RETURNS TEXT
  LANGUAGE plpgsql AS
$func$
DECLARE
   b books_table;
BEGIN
   FOREACH b IN ARRAY _books_arr
   LOOP
      INSERT INTO books_table  -- rare case where column list is no improvement
      SELECT b.*;
   END LOOP;

   RETURN 'Saved';

EXCEPTION WHEN others THEN
   RETURN 'Failure';
END
$func$;

Example call with list of row values:

SELECT update_table_variadic('(2,100,Arthur,1)', '(2,50,Zaphod,1)');

Without using VARIADIC, the function call would require a single array parameter.

Either an array literal (optionally with explicit cast):

SELECT update_table('{"(1,100,Arthur,1)","(1,50,Zaphod,1)"}'::books_table[]);

See:

Or you can use an array constructor like @a_horse demonstrates. Array literals are often easier to provide.

Major points:

Use the simpler FOREACH to loop over an array. See:

Postgres - array for loop

Avoid CaMeL-case names in Postgres unless you know what you are doing.

(Optionally) use a VARIADIC parameter to simplify the syntax for the function call. Then you can provide a list of row values. Note the maximum number of function parameters (default 100) if you go with VARIADIC:

Select rows such that names match elements of input array for pgsql function

SQL function

If you don't need to catch the exception, and you also don't need the string 'Saved' / 'Failure' to be returned, simplify:

CREATE OR REPLACE FUNCTION update_table_set(VARIADIC _books_arr books_table[])
  RETURNS void
  LANGUAGE sql AS
$func$
   INSERT INTO books_table
   SELECT * FROM unnest(_books_arr) b;
$func$;

db<>fiddle here
_{Old sqlfiddle}

Postgresql – Insert Array of JSON into Postgres Table

PART1 - INSERTING

You don't have to use unnest() but jsonb_array_elements() and add square brackets to the JSON data structure. I sugget you using a JSON validator website like JSONlint to test the correctness of your JSON data.

This codes inserts 3 new records in inputtable:

WITH json_array AS (
    SELECT 1 AS user_id, 
           2 AS chart_id,
           jsonb_array_elements('
               [
                 {
                    "col1": "a",
                    "col2": 1,
                    "col3": 1,
                    "col4": "one"
                 }, {
                    "col1": "b",
                    "col2": 2,
                    "col3": 2,
                    "col4": "two"
                 }, {
                    "col1": "c",
                    "col2": 3,
                    "col3": 3,
                    "col4": "three"
                }
               ]'::jsonb) AS properties
)
INSERT INTO inputtable (user_id, chart_id, properties) 
SELECT * FROM json_array

PART2 - UPDATING

In order to update you must specify data_point_id values, so you have to know them a priori.

This works perfectly, but probably there are other naive solutions:

WITH update_table AS(
   SELECT unnest(ARRAY[1, 2, 3]) AS data_point_id,
          jsonb_array_elements('
            [
              {
                "col1": "a",
                "col2": 6,
                "col3": 7,
                "col4": "eight"
              }, {
                "col1": "b",
                "col2": 10,
                "col3": 11,
                "col4": "twelve"
              }, {
                "col1": "c",
                "col2": 3,
                "col3": 3,
                "col4": "new"
              }
           ]'::jsonb) AS properties 
    FROM inputtable
)
UPDATE inputtable 
SET properties = update_table.properties
FROM update_table
WHERE inputtable.data_point_id = update_table.data_point_id

Best Answer

Related Solutions

Postgresql – Use array of composite type as function parameter and access it

PL/pgSQL function

SQL function

Postgresql – Insert Array of JSON into Postgres Table

PART1 - INSERTING

PART2 - UPDATING

Related Question