Hi Tom, hi David
Sorry for the late reply. When I was reading the doc, I was wondering why the is 2 ways to create an array ... now I know :D
You're so right, cause, especially since the tests are performed by a go script ( by using the placeholders ) so it's gonna be way easier to create the array at the SQL level.
Thank you so much for your precious advice and for the clarification.
Wish you a good day :)
Maxime FRYSOU <maxprocess@gmail.com> writes:
> Code speaks louder than words, so ...in order to abstract most of the
> complexity, and to focus on the syntax, the products table is obviously not
> representative of the real one. My goal here is to make a "simple" insert.
> CREATE TYPE RGB AS (R VARCHAR(5), G VARCHAR(5), B VARCHAR(5));
> CREATE TYPE color AS (rgb RGB, label VARCHAR(50));
> CREATE TABLE products (index SERIAL PRIMARY KEY, colors color []);
> And this is where it's not working ...
> INSERT INTO products (colors)
> VALUES
> (
> '{ (("18", "15", "55"), "BLACK" )',
> '("137", "231", "129"), "GREEN" )}' :: color []
> )
Yeah, you'd need to apply the quoting rules for arrays over those for
(two levels of) records, and you didn't. TBH, the easiest way to deal
with that is not to. You can build the structures at the SQL level
instead:
INSERT INTO products (colors)
VALUES(
array[ row(row('18','15','55'), 'BLACK')::color,
row(row('137','231','129'), 'GREEN')::color ]
);
If you really want to do it the hard way, one valid representation is
INSERT INTO products (colors)
VALUES
(
'{"(\"(18,15,55)\",BLACK)","(\"(137,231,129)\",GREEN)"}'::color[]
);
regards, tom lane