Paul Jungwirth <pj@xxxxxxxxxxxxxxxxxxxxxxxx> 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
