Обсуждение: EXLCUDE constraints and Hash indexes
From: https://www.postgresql.org/docs/9.4/static/sql-createtable.html "The access method must support amgettuple (see Chapter 55); at present this means GIN cannot be used. Although it's allowed, there is little point in using B-tree or hash indexes with an exclusion constraint, because this does nothing that an ordinary unique constraint doesn't do better. So in practice the access method will always be GiST or SP-GiST." This is misleading. Hash indexes do not support unique constraints directly, but do support them via the EXCLUDE syntax using "WITH =". This is nice if you want a unique index on something that might occasionally exceed 1/3 of 8kB (titin, I'm looking at you) Trivial doc patch attached. Cheers, Jeff
Вложения
>>>>> "Jeff" == Jeff Janes <jeff.janes@gmail.com> writes: Jeff> From: https://www.postgresql.org/docs/9.4/static/sql-createtable.html Jeff> "The access method must support amgettuple (see Chapter 55); atJeff> present this means GIN cannot be used. Althoughit's allowed, there isJeff> little point in using B-tree or hash indexes with an exclusionJeff> constraint, becausethis does nothing that an ordinary uniqueJeff> constraint doesn't do better. So in practice the access method willJeff>always be GiST or SP-GiST." I also recently found a case where using btree exclusion constraints was useful: a unique index on an expression can't be marked deferrable, but the equivalent exclusion constraint can be. -- Andrew (irc:RhodiumToad)
On 8/17/16 8:12 AM, Andrew Gierth wrote: > I also recently found a case where using btree exclusion constraints was > useful: a unique index on an expression can't be marked deferrable, but > the equivalent exclusion constraint can be. That seems well worth documenting... -- Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX Experts in Analytics, Data Architecture and PostgreSQL Data in Trouble? Get it in Treble! http://BlueTreble.com 855-TREBLE2 (855-873-2532) mobile: 512-569-9461