CREATE TYPE pencil_count AS(
pencil_color varchar(30),
count integer
);
CREATE TYPE pencil_count_with_date(
date_ date,
pencil_count pencil_count[]
);
CREATE TABLE pencils(id serial, pencils_ pencil_count_with_date[]);
INSERT INTO pencils(pencils_)
VALUES('{"(\"2016-03-13\",{"(\"blue\",1)","(\"red\",2)"})"}');
What would be the correct syntax if I want to add this composite array without using ARRAY[...]
?
Using literal string will be less readable when you add new nested levels:
CREATE TYPE pencil_count AS(pencil_color varchar(30)
,"count" int);
CREATE TYPE pencil_count_with_date AS(date_ date
,pencil_count pencil_count[]);
CREATE TABLE pencils(id serial, pencils_ pencil_count_with_date[]);
INSERT INTO pencils(pencils_)
VALUES('{"(
\"2016-03-13\",
\"{
\"\"(Blue,5)\"\",
\"\"(Red,2)\"\"
}\"
)"}');
SELECT pencils_[1].pencil_count[1].pencil_color
FROM pencils;
Remarks:
"
escaped by \
based on nest level.$$
if needed.ARRAY
and ROW
could be easier to spot where each level starts and stops.