Re: [GENERAL] Accessing composite type columns in indexes

2006-03-03 Thread Michael Glaesemann
On Mar 4, 2006, at 14:17 , Tom Lane wrote: The UNIQUE/PRIMARY KEY syntax only allows bare column names, per the SQL standard. While there's not any technical reason why we couldn't extend that syntax, there's a pretty large fear factor involved: we could find ourselves behind the spec-co

Re: [GENERAL] Accessing composite type columns in indexes

2006-03-03 Thread Tom Lane
Michael Glaesemann <[EMAIL PROTECTED]> writes: > Except, why doesn't it work in the CREATE TABLE statement? The UNIQUE/PRIMARY KEY syntax only allows bare column names, per the SQL standard. While there's not any technical reason why we couldn't extend that syntax, there's a pretty large fear fac

Re: [GENERAL] Accessing composite type columns in indexes

2006-03-03 Thread Michael Glaesemann
On Mar 4, 2006, at 13:44 , Michael Glaesemann wrote: On Mar 4, 2006, at 13:31 , Tom Lane wrote: Make sense now? Yep! Except, why doesn't it work in the CREATE TABLE statement? One needs to add the UNIQUE index as a separate command. For example, -- doesn't work create table foo (

Re: [GENERAL] Accessing composite type columns in indexes

2006-03-03 Thread Michael Glaesemann
On Mar 4, 2006, at 13:34 , Michael Fuhr wrote: On Sat, Mar 04, 2006 at 12:21:38PM +0900, Michael Glaesemann wrote: create unique index employment_history_pkey_idx on employment_history (company, (during).from_date, (during).to_date); ERROR: syntax error at or near "." at character 89 LINE

Re: [GENERAL] Accessing composite type columns in indexes

2006-03-03 Thread Michael Fuhr
On Sat, Mar 04, 2006 at 12:21:38PM +0900, Michael Glaesemann wrote: > create unique index employment_history_pkey_idx > on employment_history (company, (during).from_date, (during).to_date); > ERROR: syntax error at or near "." at character 89 > LINE 2: on employment_history (company, (during).fro

Re: [GENERAL] Accessing composite type columns in indexes

2006-03-03 Thread Tom Lane
Michael Glaesemann <[EMAIL PROTECTED]> writes: > ... it appears that one can't directly access the columns of a > composite type when creating an index, i.e., neither UNIQUE (foo.bar) > nor UNIQUE ((foo).bar) work. You need both, ie something like create table foo (bar date_co_interval); crea

[GENERAL] Accessing composite type columns in indexes

2006-03-03 Thread Michael Glaesemann
Michael Fuhr's example of using composite types for date intervals/ ranges/periods prompted me to explore this a little further. While doing so, it appears that one can't directly access the columns of a composite type when creating an index, i.e., neither UNIQUE (foo.bar) nor UNIQUE ((foo).