Re: [GENERAL] Working with Array of Composite Type
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 ( idsmallint, user_id int references users (user_id) caption text, is_primaryboolean, is_privateboolean ); 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] 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 ( idsmallint, caption text, is_primaryboolean, is_privateboolean ); CREATE TABLE users ( user_id serial NOT NULL, curr_countsmallint,-- just an image identifier imagesimage[] ); -- 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
Re: [GENERAL] Working with Array of Composite Type
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 ( idsmallint, user_id int references users (user_id) caption text, is_primaryboolean, is_privateboolean ); -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
