Re: Primary key gist index?

On 03/14/2018 11:10 AM, Jeremy Finzel wrote:
>
> On Wed, Mar 14, 2018 at 8:33 AM, Adrian Klaver
> <adrian(dot)klaver(at)aklaver(dot)com <mailto: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
> <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 <mailto: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.

https://www.postgresql.org/docs/10/static/sql-createtable.html#SQL-CREATETABLE-EXCLUDE

"The EXCLUDE clause defines an exclusion constraint, which guarantees
that if any two rows are compared on the specified column(s) or
expression(s) using the specified operator(s), not all of these
comparisons will return TRUE. If all of the specified operators test for
equality, this is equivalent to a UNIQUE constraint, although an
ordinary unique constraint will be faster. ..."

I have always taken the above to mean that while is possible to create
an EXCLUDE that enforces uniqueness that operation is internal to the
EXCLUDE and is not knowable to Postgres when it is looking for a UNIQUE
index. Probably because an EXCLUDE can be non-unique.

>
> Thanks,
> Jeremy

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com