arrayspostgresqlpostgresql-9.4

Correct syntax for array of composite type


CREATE TYPE pencil_count AS(
    pencil_color varchar(30),
    count integer
);

CREATE TABLE pencils(id serial, pencils_ pencil_count[]);

INSERT INTO pencils(pencils_) VALUES('{("blue",5),("red",2)}');

This doesn't work and gives error:

Malformed array literal.

What would be the correct syntax if I want to add this composite array without using ARRAY[...]?


Solution

  • I want to add this composite array without using ARRAY

    You could use:

    INSERT INTO pencils(pencils_) 
    VALUES('{"(\"blue\",5)","(\"red\",2)"}');
    

    db<>fiddle demo

    Row type

    Remember that what you write in an SQL command will first be interpreted as a string literal, and then as a composite. This doubles the number of backslashes you need (assuming escape string syntax is used).

    The string-literal processor removes one level of backslashes.

    The ROW constructor syntax is usually easier to work with than the composite-literal syntax when writing composite values in SQL commands. In ROW, individual field values are written the same way they would be written when not members of a composite.