Re: Primary key gist index?

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 &&)

I'm curious why you need a primary key on this table, especially if the 
exclusion constraint is already preventing duplicate/overlapping records?

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've been on-and-off 
building an extension for temporal foreign keys at [1]. That is pretty 
new, but perhaps it will be useful/interesting to you. And if you have 
any feedback, I'd love to hear it!

But anyway, maybe if you shared why the table needs a real PRIMARY KEY, 
people here can suggest something.

[1] https://github.com/pjungwir/time_for_keys

Yours,

-- 
Paul              ~{:-)
pj@illuminatedcomputing.com