Re: Composite types and tables - when to use?

On Tue, Oct 6, 2009 at 4:43 PM, Postgres User
<postgres(dot)developer(at)gmail(dot)com> wrote:
> Browsing the docs last night, I realized that I've never taken
> advantage of Postgres' powerful composite types.  But a question came
> to mind- in what scenarios should you use a composite type in a table
> structure?  That is, I can see the benefits of a composiite type
> insofar as it essentially lets you add virtual  tuples to a table
> without having to alter the table structure to add new fields.
> Instead you can simply extend the composite type.  But why take this
> approach?

First, a quick clarification. You can only alter composite types if
they are defined with 'create table', not 'create type as'. For this
reason, I always create my composites with 'create table'.

Using composites inside a table structure can be useful but is easily
abused. There are useful cases though: the built in geo types are all
basically composite types, in that they are grouped POD types. The
rules of thumb for using composite/array inside a table are:

*) Are you sure you are not adding structure for no reason?
*) Does your data always get written/read at the same time?
*) If you were to, say, use a foreign key, would you link it to the
composite itself and not its inner fields?
*) Do you not care that related changes elsewhere in the schema can
give you major headaches?
*) Does your data naturally group in a way such that it feels like a
single element?
*) Does your client app have ability to deal with composites without
dealing with (much nastier than you think) parsing?

If you answered 'yes' to all of the above, maybe a composite type (or,
in similarly, an array) is worth considering. Composites work
absolute wonders in passing data to/from functions, and can be useful
for lots of other things.

Just a heads up: if you are writing your application in C and are
looking for an effective way of dealing with composites in the client,
check out libpqtypes (http://pgfoundry.org/projects/libpqtypes/). No
parsing! :-).

merlin