Working with Array of Composite Type

Started by Alex Magnumabout 11 years ago3 messagesgeneral
Jump to latest
#1Alex Magnum
magnum11200@gmail.com

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

#2Jan de Visser
jan@de-visser.net
In reply to: Alex Magnum (#1)
Re: 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 (
id smallint,
user_id int references users (user_id)
caption text,
is_primary boolean,
is_private boolean
);

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

#3Jim Nasby
Jim.Nasby@BlueTreble.com
In reply to: Jan de Visser (#2)
Re: 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 (
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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general