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. 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
