On Wed, Sep 21, 2016 at 08:44:15PM +0100, Geoff Winkless wrote: > On 21 September 2016 at 13:29, Robert Haas <robertmh...@gmail.com> wrote: > > I'd be curious what benefits people expect to get. > > An edge case I came across the other day was a unique index on a large > string: postgresql popped up and told me that I couldn't insert a > value into the field because the BTREE-index-based constraint wouldn't > support the size of string, and that I should use a HASH index > instead. Which, of course, I can't, because it's fairly clearly > deprecated in the documentation...
Thanks for that. Forgot about that bit of nastiness. I came across the above migrating a MySQL app to PostgreSQL. MySQL, I believe, handles this by silently truncating the string on index. PostgreSQL by telling you it can't index. :( So, as a result, AFAIK, I had a choice between a trigger that did a left() on the string and inserts it into a new column on the table that I can then index or do an index on left(). Either way you wind up re-writing a whole bunch of queries. If I wanted to avoid the re-writes I had the option of making the DB susceptible to poor recovery from crashes, et all. No matter which option I chose, the end result was going to be ugly. It would be good not to have to go ugly in such situations. Sometimes one size does not fit all. For me this would be a second major case where I'd use usable hashed indexes the moment they showed up. Andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
