Re: insert in an array of composite type

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(at)gmail(dot)com <maxprocess(at)gmail(dot)com>Tél : +33 (*0)6 49 66 70 69
http://fr.linkedin.com/in/*maximefrysou*
<http://fr.linkedin.com/in/maximefrysou>

<http://www.doyoubuzz.com/maxime-frysou>

On Sun, 26 Apr 2020 at 00:23, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Maxime FRYSOU <maxprocess(at)gmail(dot)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
>