On 02/26/2017 09:42 AM, Tom Lane wrote:
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.
Alright, thanks. It is just that with array/hstore/json(b) I see a table
in a column in a table and I need to be clear in my mind what is being
referred to.
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
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
