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(at)illuminatedcomputing(dot)com