Search Postgresql Archives

Re: ERROR: functions in index expression must be marked IMMUTABLE

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux