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.
CREATETYPE RGB AS (R VARCHAR(5), G VARCHAR(5), B VARCHAR(5));
CREATETYPE color AS (rgb RGB, label VARCHAR(50));
CREATETABLE products (index SERIALPRIMARYKEY, colors color []);
And this is where it's not working ...
INSERTINTO 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.