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[...]
?
I want to add this composite array without using ARRAY
You could use:
INSERT INTO pencils(pencils_)
VALUES('{"(\"blue\",5)","(\"red\",2)"}');
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.