On Fri, Nov 15, 2013 at 2:01 AM, Tony Theodore <tony.theodore@xxxxxxxxx> wrote: > Hi, > > I was reading about composite types and wondering if I should use them instead of composite keys. I currently have tables like this: > > create table products ( > source_system text, > product_id text, > description text, > ... > primary key (source_system, product_id) > ); > create table inventory ( > source_system text, > product_id text, > qty int, > ... > foreign key (source_system, product_id) references products > ); > > > and it means having to add the “source_system" column to many queries. Would something like: > > create type product as ( > source_system text, > product_id text > ); > create table products ( > product product, > description text, > ... > primary key(product) > ); > create table inventory ( > product product, > qty numeric, > ... > foreign key (product) references products > ); > > be a correct use of composite types? I rarely need to see the columns separately, so having to write “(product).product_id” won’t happen much in practice. Well, here are the downsides. Composite types: *) are more than the sum of their parts performance-wise. So there is a storage penalty in both the heap and the index *) can't leverage indexes that are querying only part of the key *) will defeat the implicit 'per column NOT NULL constraint' of the primary keys *) are not very well supported in certain clients -- for example JAVA. you can always deal with them as text, but that can be a headache. ...plus some other things I didn't think about. If you can deal with those constraints, it might be interesting to try a limited experiment. The big upside of composite types is that you can add attributes on the fly without rebuilding the index. Test carefully. merlin -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
