agora inbox for pgsql-sql@postgresql.org  

help / color / mirror / Atom feed

From: Tom Lane <tgl@sss.pgh.pa.us>
To: Maxime FRYSOU <maxprocess@gmail.com>
Cc: pgsql-sql@lists.postgresql.org
Subject: Re: insert in an array of composite type
Date: Sat, 25 Apr 2020 18:22:59 -0400
Message-ID: <16634.1587853379@sss.pgh.pa.us> (raw)
In-Reply-To: <CAA8etTmBqYjAQZHBuYM6ZGYbWd-aW9iSrsNTvGjX0WR1WdeXvQ@mail.gmail.com>
References: <CAA8etTmBqYjAQZHBuYM6ZGYbWd-aW9iSrsNTvGjX0WR1WdeXvQ@mail.gmail.com>

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

view thread (4+ messages)  latest in thread

Message-ID: <16634.1587853379@sss.pgh.pa.us>
Permalink:  ../16634.1587853379@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/16634.1587853379@sss.pgh.pa.us

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: tgl@sss.pgh.pa.us, maxprocess@gmail.com, pgsql-sql@lists.postgresql.org
  Subject: Re: insert in an array of composite type
  In-Reply-To: <16634.1587853379@sss.pgh.pa.us>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox