Thread: insert in an array of composite type
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 []
)
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);
--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.
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
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 :)
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