On Wed, Mar 14, 2018 at 8:33 AM, Adrian Klaver <adrian.klaver@xxxxxxxxxxx> 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.klaver@xxxxxxxxxxx
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
