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