Re: Working with Array of Composite Type - Mailing list pgsql-general

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
);