Re: Primary key gist index?

Paul Jungwirth <pj(at)illuminatedcomputing(dot)com> writes:
> On 03/14/2018 06:19 AM, Jeremy Finzel wrote:
>> Hello! From all that I can tell, it is not possible using a btree_gist
>> index as a primary key. If so, why not? I have a table with this gist
>> index which truly ought to be its primary key. as_of_date is of range
>> date type:
>>
>> EXCLUDE USING gist (id WITH =, as_of_date WITH &&)

> Technically I think an exclusion constraint (or at least this one)
> fulfills the formal requirements of a primary key (is unique, isn't
> null), but maybe there are other primary-key duties it doesn't meet,
> like defining foreign keys that reference it.

I think a key point is that an exclusion constraint might or might not
provide behavior that could be construed as uniqueness. The logic for
PRIMARY KEY hasn't got nearly enough knowledge to tell whether particular
operators used in a particular way in a GIST index will behave in a way
that would support calling that a primary key. b-tree indexes, on the
other hand, have basically only one behavior, so they're easy.

Also, as you mention, extrapolating behavior that's not really equality
to situations like foreign keys gets pretty interesting pretty fast.
An exclusion constraint using && might ensure that no two values in the
column are identical, but it would not be enough to ensure that a proposed
FK row can't && with more than one PK row. So what then?

regards, tom lane