agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedinsert in an array of composite type
4+ messages / 3 participants
[nested] [flat]
* insert in an array of composite type
@ 2020-04-25 21:45 Maxime FRYSOU <maxprocess@gmail.com>
0 siblings, 2 replies; 4+ messages in thread
From: Maxime FRYSOU @ 2020-04-25 21:45 UTC (permalink / raw)
To: pgsql-sql@lists.postgresql.org
Hi guys,
I'm back to the world of development and saying that I struggle these days
is a euphemism but I guess it's the price to pay after 7 years without
coding :)
I'm trying to experiment with the Array type along with the composite
types. I've read that most of the times we think about arrays of
composites, we may reconsider the problem and maybe opt for creating a
distinct table...
But, with my example, it is not that obvious cause the array is a field
containing the 2 predominant colours of a product. Each colour is
represented by 2 fields: RGB and a label.
Because indexes are available in arrays, as well as on a column within an
array I wanted to test the feature. In this scenario, it's a 2-items
array, values won't change once the record is inserted. 95% of the
operations on this array would be reading the values as it is and filtering
on the 2 first items without any calculation or conversions.
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 []
)
Thanks for your help :)
Best.
<http://www.doyoubuzz.com/maxime-frysou;
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: insert in an array of composite type
@ 2020-04-25 22:15 David G. Johnston <david.g.johnston@gmail.com>
parent: Maxime FRYSOU <maxprocess@gmail.com>
1 sibling, 0 replies; 4+ messages in thread
From: David G. Johnston @ 2020-04-25 22:15 UTC (permalink / raw)
To: Maxime FRYSOU <maxprocess@gmail.com>; +Cc: pgsql-sql <pgsql-sql@lists.postgresql.org>
On Sat, Apr 25, 2020 at 2:45 PM Maxime FRYSOU <maxprocess@gmail.com> wrote:
> 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 []
> )
>
>
If you are going to do this in pure SQL (i.e., INSERT/VALUES) going from a
string to the color array is the tedious way to do it. Just do it
directly, for example:
--create type comp_in as (a text, b text);
--create type comp_out as (ci comp_in, lbl text);
select array[
( ('a', 'b')::comp_in,
'lbl'
)::comp_out
]::comp_out[]
Regardless the text output is basically what you want if you do find the
need to represent the value as a single string.
select '{"(\"(a,b)\",lbl)"}'::comp_out[]
David J.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: insert in an array of composite type
@ 2020-04-25 22:22 Tom Lane <tgl@sss.pgh.pa.us>
parent: Maxime FRYSOU <maxprocess@gmail.com>
1 sibling, 1 reply; 4+ messages in thread
From: Tom Lane @ 2020-04-25 22:22 UTC (permalink / raw)
To: Maxime FRYSOU <maxprocess@gmail.com>; +Cc: pgsql-sql@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: insert in an array of composite type
@ 2020-04-26 06:57 Maxime FRYSOU <maxprocess@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 4+ messages in thread
From: Maxime FRYSOU @ 2020-04-26 06:57 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql@lists.postgresql.org
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 <maxprocess@gmail.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@sss.pgh.pa.us> wrote:
> 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
>
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2020-04-26 06:57 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-04-25 21:45 insert in an array of composite type Maxime FRYSOU <maxprocess@gmail.com>
2020-04-25 22:15 ` David G. Johnston <david.g.johnston@gmail.com>
2020-04-25 22:22 ` Tom Lane <tgl@sss.pgh.pa.us>
2020-04-26 06:57 ` Maxime FRYSOU <maxprocess@gmail.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox