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[...]?
Advice so far is not optimal. There is a simpler solution and actually applicable explanation.
When in doubt, just ask Postgres to show you:
CREATE TEMP TABLE pencil_count ( -- table also registers row type
pencil_color varchar(30)
, count integer
);
CREATE TEMP TABLE pencils (
id serial
, pencils_ pencil_count[]
);
Insert 2 basic rows:
INSERT INTO pencil_count VALUES ('red', 1), ('blue', 2);
See the syntax for the basic row type:
SELECT p::text AS p_row FROM pencil_count p;
p_row
----------
(red,1)
(blue,2)
See the syntax for an array of rows:
SELECT ARRAY(SELECT p FROM pencil_count p)::text AS p_row_arr;
p_row_arr
------------------------
{"(red,1)","(blue,2)"}
All you need is to enclose each row literal in double quotes - which is only necessary to disable the special meaning of the comma within each row type.
Additional (escaped) double quotes would be redundant noise while there are no additional special characters.
None of this has anything to do with escape string syntax, which has been turned off by default since Postgres 9.1. You would have to declare escape string syntax explicitly by prefixing E, like E'string\n'. But there is no good reason to do that.
Related answer with more explanation:
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.
