Search Postgresql Archives

Re: Selecting strict, immutable text for a composite type.

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Thu, 10 May 2018 14:41:26 -0400
Tom Lane <tgl@xxxxxxxxxxxxx> wrote:

> [ please keep the list cc'd ]
> 
> Steven Lembark <lembark@xxxxxxxxxxx> writes:
> > On Thu, 10 May 2018 11:52:48 -0400
> > Tom Lane <tgl@xxxxxxxxxxxxx> wrote:  
> >> Maybe you should show a more concrete example of what's not
> >> working.  
> 
> > The problem is with gists telling me that they cannot index
> > the type. This works for enums, just not the composite type.  
> 
> Oh, well, they can't.  There's no GiST opclass covering arbitrary
> composite types.  This doesn't seem very surprising to me given
> the lack of operators that such an opclass might accelerate.

But I thought that they could include functions of composite
types that were indexable (e.g., text)?

e.g., enums.

 
> What are you expecting an index on such a column to do for you?
> If you just want a uniqueness constraint, plain btree can handle it.

The composite participates in an exclusion constraint:

    location
        lat_lng_t
        not null
    ,
    effective   
        tstzrange
        not null
        defualt tstzrange( now(), 'infinity', '(]' )
    ,
    exclude using gist
    (
        location    using =,
        effective   using &&
    )

i.e., the time series can have only one effective set of 
data for any one period.

So far as I knew it was possible to have a function on the
type that produced an indexable type (e.g., text). This worked
for the enums, I thought it would work for a composite: produce
a text value that is indexable.

Or is the declaration of the exclusion with a function rather
than the column? Or a function rather than '='? Or a separate
declaration that describes comparing the composite type that
allows the gist to work?



-- 
Steven Lembark                                       1505 National Ave
Workhorse Computing                                 Rockford, IL 61103
lembark@xxxxxxxxxxx                                    +1 888 359 3508




[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux