Re: insert in an array of composite type - Mailing list pgsql-sql

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