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 []
)