On 02/26/2017 08:50 AM, Tom Lane wrote:
Geoff Winkless <pgsqladmin@xxxxxxxx> writes:
On 26 February 2017 at 16:09, Adrian Klaver <adrian.klaver@xxxxxxxxxxx>
wrote:
On 02/26/2017 07:56 AM, Geoff Winkless wrote:
On 26 February 2017 at 10:09, Sven R. Kunze <srkunze@xxxxxxx
<mailto:srkunze@xxxxxxx>>wrote:
# create index docs_birthdate_idx ON docs using btree
(((meta->>'birthdate')::date));
ERROR: functions in index expression must be marked IMMUTABLE
Date functions are inherently not immutable because of timezones.
Isn't the point that casting to ::timestamp will still keep the
timezone? Hence casting to "without timezone".
There are multiple reasons why the text-to-datetime conversion functions
are not immutable:
* some of them depend on the current timezone (but I don't believe date_in
does);
* all of them depend on the current datestyle setting, eg to resolve
'02/03/2017';
* all of them accept strings with time-varying values, such as 'now'
or 'today'.
You could get around the second and third points with to_timestamp(),
but since the only variant of that is one that yields timestamptz and
hence is affected by the timezone setting, it's still not immutable.
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?
or
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?
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
