Adrian Klaver <adrian.klaver@xxxxxxxxxxx> writes: > On 02/26/2017 08:50 AM, Tom Lane wrote: >> I'm not entirely sure why the OP feels he needs an index on this >> expression. If he's willing to restrict the column to have the >> exact format 'YYYY-MM-DD', then a regular textual index would sort >> the same anyway. Perhaps what's needed is just to add a CHECK >> constraint verifying that the column has that format. > The OP is trying to create an index on the value of a jsonb key. Would > the above still apply or am I misunderstanding the reference to column? Sure, I was using "column" loosely to refer to the meta->>'birthdate' expression. > The below works: > test=> create index docs_birthdate_idx ON docs using btree > ((meta->>'birthdate')); > CREATE INDEX > So if the text values of 'birthdate' are consistent the index would work > without the cast? Yeah, seems to me you could do things like ... WHERE meta->>'birthdate' > '2017-02-26' and it would Just Work, though I'd admit there's a deficiency of sanity checking for the RHS constant in this example. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
