From: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
---|---|
To: | "Sven R(dot) Kunze" <srkunze(at)mail(dot)de>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: ERROR: functions in index expression must be marked IMMUTABLE |
Date: | 2017-02-28 14:40:14 |
Message-ID: | b88429d9-4b0e-e028-6db4-5f36a8143a5e@aklaver.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 02/28/2017 01:35 AM, Sven R. Kunze wrote:
> On 27.02.2017 18:17, Adrian Klaver wrote:
>> Yes, but is not about timezone dependency, it is about the other
>> dependencies listed in the second and third points. Namely the
>> datestyle setting and magic strings e.g. 'now'
>
> I am sorry, I still don't understand. to_date and to_timestamp require
> datestyle settings per se and magic strings don't work.
See here:
https://www.postgresql.org/message-id/11190.1488127834%40sss.pgh.pa.us
"There are multiple reasons why the text-to-datetime conversion
functions are not immutable"
Tom was referring to the text --> date cast you where attempting in your
original index definition:
create index docs_birthdate_idx ON docs using btree
(((meta->>'birthdate')::date));
So:
test=> select 'today'::date;
date
------------
2017-02-28
(1 row)
test=> select 'now'::date;
date
------------
2017-02-28
(1 row)
test=> set datestyle = 'SQL, DMY';
SET
test=> select 'today'::date;
date
------------
28/02/2017
(1 row)
test=> select 'now'::date;
date
------------
28/02/2017
(1 row)
Now you tried to work around the casting issue by using to_timestamp:
create index docs_birthdate_idx ON docs using btree
((to_timestamp(meta->>'birthdate', 'YYYY-MM-DD') at time zone 'UTC'));
but that introduced the issue that to_timestamp returns a timestamptz
and so you end up with a dependency on timezones.
>
>
> =# -- required datestyle
> =# select to_date('2000-01-01');
> ERROR: function to_date(unknown) does not exist
> LINE 1: select to_date('2000-01-01');
> ^
> HINT: No function matches the given name and argument types. You might
> need to add explicit type casts.
>
>
> =# -- magic strings don't work
> =# select to_date('');
> ERROR: invalid value "epoc" for "YYYY"
> DETAIL: Value must be an integer.
> =# select to_date('epoch', 'YYYY-MM-DD');
> ERROR: invalid value "epoc" for "YYYY"
> DETAIL: Value must be an integer.
> =# select to_date('infinity', 'YYYY-MM-DD');
> ERROR: invalid value "infi" for "YYYY"
> DETAIL: Value must be an integer.
> =# select to_date('-infinity', 'YYYY-MM-DD');
> ERROR: invalid value "-inf" for "YYYY"
> DETAIL: Value must be an integer.
> =# select to_date('now', 'YYYY-MM-DD');
> ERROR: invalid value "now" for "YYYY"
> DETAIL: Value must be an integer.
> =# select to_date('today', 'YYYY-MM-DD');
> ERROR: invalid value "toda" for "YYYY"
> DETAIL: Value must be an integer.
> =# select to_date('tomorrow', 'YYYY-MM-DD');
> ERROR: invalid value "tomo" for "YYYY"
> DETAIL: Value must be an integer.
> =# select to_date('yesterday', 'YYYY-MM-DD');
> ERROR: invalid value "yest" for "YYYY"
> DETAIL: Value must be an integer.
> =# select to_date('allballs', 'YYYY-MM-DD');
> ERROR: invalid value "allb" for "YYYY"
> DETAIL: Value must be an integer.
>
> Regards,
> Sven
--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | Adrian Klaver | 2017-02-28 15:06:31 | Re: is (not) distinct from |
Previous Message | Albe Laurenz | 2017-02-28 14:33:24 | Re: Question about TOAST table - PostgreSQL 9.2 |