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