Re: Primary key gist index?

On Wed, Mar 14, 2018 at 8:33 AM, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
wrote:

> 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
>>
>
> https://www.postgresql.org/docs/10/static/btree-gist.html
>
> "In general, these operator classes will not outperform the equivalent
> standard B-tree index methods, and they lack one major feature of the
> standard B-tree code: the ability to enforce uniqueness."
>
>
> 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 &&)
>>
>> Any direction here would be much appreciated.
>>
>> Right now, I am forced to create a redundant btree index UNIQUE, btree
>> (id, lower(as_of_date)) in order to have a primary key on the table.
>>
>> Thanks!
>> Jeremy
>>
>
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)aklaver(dot)com
>

Thank you for the ref. But I don't understand how an exclusion constraint
does not have "the ability to enforce uniqueness" unless they just mean
that is the case "under the covers of postgres". That is exactly what it
does, right? By the definition of the exclusion index I have above, there
cannot be more than one row with the same id and as_of_date values.

Thanks,
Jeremy