Thread: Working with Array of Composite Type
Hello,
I am struggling with finding the right way to deal with arrays of composite types. Bellow is an example of the general setup where I defined an image type to describe the image properties. A user can have mulitple images stored.
What is the best way to:
a) search for an image within the array
e.g. WHERE image.id = 3
WHERE is_private IS TRUE
b) to update an image inside the array.
e.g. is_private = FALSE WHERE image.id = 2
c) to delete an image why its id
e.g. WHERE image.id=2
d) to create a listing like
in the unset, but with the field names of the type
e.g.
user_id | id | caption | is_primary | is_private
---------+----+-----------------+------------+-------------
1 | 1 | This is Image A | f | f
1 | 2 | This is Image B | f | f
CREATE TYPE image AS (
id smallint,
caption text,
is_primary boolean,
is_private boolean
);
CREATE TABLE users (
user_id serial NOT NULL,
curr_count smallint, -- just an image identifier
images image[]
);
-- create the initial user record
INSERT INTO users VALUES (default,0,null);
-- inserting new elements
UPDATE users SET curr_count=curr_count+1, images=images || ARRAY[row(curr_count+1,'This is Image A',false,false)::image] WHERE user_id=1;
UPDATE users SET curr_count=curr_count+1, images=images || ARRAY[row(curr_count+1,'This is Image B',false,false)::image] WHERE user_id=1;
UPDATE users SET curr_count=curr_count+1, images=images || ARRAY[row(curr_count+1,'This is Image C',false,true)::image] WHERE user_id=1;
UPDATE users SET curr_count=curr_count+1, images=images || ARRAY[row(curr_count+1,'This is Image D',true,false)::image] WHERE user_id=1;
-- list the images
SELECT user_id,curr_count,unnest(images) as limages from users WHERE user_id=1;
SELECT user_id,curr_count,unnest(images) as limages from users WHERE user_id=1;
user_id | curr_count | limages
---------+------------+---------------------------
1 | 4 | (1,"This is Image A",f,f)
1 | 4 | (2,"This is Image B",f,f)
1 | 4 | (3,"This is Image C",f,t)
1 | 4 | (4,"This is Image D",t,f)
Any help or suggestion on this topic is highly appreciated.
Thanks
Alex
On March 28, 2015 06:18:49 PM Alex Magnum wrote: > Hello, > I am struggling with finding the right way to deal with arrays of composite > types. Bellow is an example of the general setup where I defined an image > type to describe the image properties. A user can have mulitple images > stored. The canonical answer is that in almost all cases where you think you want an array of composites, you *really* want a table join: i.e. turn your image *type* into an image *table* with the user_id as a foreign key. CREATE TABLE users ( user_id serial NOT NULL, ); CREATE TABLE image ( id smallint, user_id int references users (user_id) caption text, is_primary boolean, is_private boolean );
On 3/28/15 9:36 AM, Jan de Visser wrote: > On March 28, 2015 06:18:49 PM Alex Magnum wrote: >> Hello, >> I am struggling with finding the right way to deal with arrays of composite >> types. Bellow is an example of the general setup where I defined an image >> type to describe the image properties. A user can have mulitple images >> stored. > > The canonical answer is that in almost all cases where you think you want an > array of composites, you *really* want a table join: > > i.e. turn your image *type* into an image *table* with the user_id as a > foreign key. > > CREATE TABLE users ( > user_id serial NOT NULL, > ); > > CREATE TABLE image ( > id smallint, > user_id int references users (user_id) > caption text, > is_primary boolean, > is_private boolean > ); Another option is to use unnest() to turn the array into a recordset, which you can then use SQL on. If the array is quite small you might get away with that. But if you're actually storing images you'll probably be pretty unhappy with performance, because every time you make ANY change to that array you'll need to completely re-write the *entire* array to disk. -- Jim Nasby, Data Architect, Blue Treble Consulting Data in Trouble? Get it in Treble! http://BlueTreble.com