ERROR: functions in index expression must be marked IMMUTABLE

On 26 February 2017 at 10:09, Sven R. Kunze <srkunze@mail.de> wrote:

# create index docs_birthdate_idx ON docs using btree

(((meta->>'birthdate')::date));
ERROR: functions in index expression must be marked IMMUTABLE

So, what is the problem here?

​Date functions are inherently not immutable because of timezones. Your
solution of using to_timestamp doesn't help because it automatically
returns a value in WITH TIMESTAMP. Do you get anywhere by using
"::timestamp without time zone" instead, as suggested here?

/messages/by-id/4E039D16.20704@pinpointresearch.com

Geoff

​

On 02/26/2017 07:56 AM, Geoff Winkless wrote:

On 26 February 2017 at 10:09, Sven R. Kunze <srkunze@mail.de
<mailto:srkunze@mail.de>>wrote:

# create index docs_birthdate_idx ON docs using btree

(((meta->>'birthdate')::date));
ERROR: functions in index expression must be marked IMMUTABLE

So, what is the problem here?

​Date functions are inherently not immutable because of timezones. Your
solution of using to_timestamp doesn't help because it automatically
returns a value in WITH TIMESTAMP. Do you get anywhere by using
"::timestamp without time zone" instead, as suggested here?

My attempts at working the OP's problem passed through that:

test=> create index docs_birthdate_idx ON docs using btree (((meta->>'birthdate')::timestamp));
ERROR: functions in index expression must be marked IMMUTABLE

This works:

test=> create index docs_birthdate_idx ON docs using btree ((meta->>'birthdate'));
CREATE INDEX

It is the act of casting that fails. Other then the OP's own suggestion of creating
a function that wraps the operation and marks it immutable I don't have a solution at
this time.

/messages/by-id/4E039D16.20704@pinpointresearch.com

Geoff

​

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 26 February 2017 at 16:09, Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

On 02/26/2017 07:56 AM, Geoff Winkless wrote:

On 26 February 2017 at 10:09, Sven R. Kunze <srkunze@mail.de
<mailto:srkunze@mail.de>>wrote:

# create index docs_birthdate_idx ON docs using btree

(((meta->>'birthdate')::date));
ERROR: functions in index expression must be marked IMMUTABLE

So, what is the problem here?

​Date functions are inherently not immutable because of timezones. Your
solution of using to_timestamp doesn't help because it automatically
returns a value in WITH TIMESTAMP. Do you get anywhere by using
"::timestamp without time zone" instead, as suggested here?

​Of course I meant "WITH TIMEZONE" here, finger slippage.

​

My attempts at working the OP's problem passed through that:

​​Apologies, I don't have that reply in the thread in my mailbox.

​

test=> create index docs_birthdate_idx ON docs using btree
(((meta->>'birthdate')::timestamp));
ERROR: functions in index expression must be marked IMMUTABLE

​ Isn't the point that casting to ::timestamp will still keep the
timezone? Hence casting to "without timezone".

This works:

test=> create index docs_birthdate_idx ON docs using btree
((meta->>'birthdate'));
CREATE INDEX

It is the act of casting that fails. Other then the OP's own suggestion of
creating
a function that wraps the operation and marks it immutable I don't have a
solution at
this time

​I can imagine that without a cast, depending on the way birthdate is
stored, it may behave differently to a cast index for ordering.

Geoff

Geoff Winkless <pgsqladmin@geoff.dj> writes:

On 26 February 2017 at 16:09, Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

On 02/26/2017 07:56 AM, Geoff Winkless wrote:

On 26 February 2017 at 10:09, Sven R. Kunze <srkunze@mail.de
<mailto:srkunze@mail.de>>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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 02/26/2017 08:15 AM, Geoff Winkless wrote:

On 26 February 2017 at 16:09, Adrian Klaver <adrian.klaver@aklaver.com
<mailto:adrian.klaver@aklaver.com>>wrote:

On 02/26/2017 07:56 AM, Geoff Winkless wrote:

On 26 February 2017 at 10:09, Sven R. Kunze <srkunze@mail.de <mailto:srkunze@mail.de>
<mailto:srkunze@mail.de <mailto:srkunze@mail.de>>>wrote:

# create index docs_birthdate_idx ON docs using btree

(((meta->>'birthdate')::date));
ERROR: functions in index expression must be marked IMMUTABLE

So, what is the problem here?

​Date functions are inherently not immutable because of timezones. Your
solution of using to_timestamp doesn't help because it automatically
returns a value in WITH TIMESTAMP. Do you get anywhere by using
"::timestamp without time zone" instead, as suggested here?

​Of course I meant "WITH TIMEZONE" here, finger slippage.

That does not work either:

test=> create index docs_birthdate_idx ON docs using btree
(((meta->>'birthdate')::timestamptz));
ERROR: functions in index expression must be marked IMMUTABLE

​

My attempts at working the OP's problem passed through that:

​​Apologies, I don't have that reply in the thread in my mailbox.

No apologies needed I had not posted my attempts at that point. It was
more me thinking out loud.

​

test=> create index docs_birthdate_idx ON docs using btree
(((meta->>'birthdate')::timestamp));
ERROR: functions in index expression must be marked IMMUTABLE

​ Isn't the point that casting to ::timestamp will still keep the
timezone? Hence casting to "without timezone".

This works:

test=> create index docs_birthdate_idx ON docs using btree
((meta->>'birthdate'));
CREATE INDEX

It is the act of casting that fails. Other then the OP's own
suggestion of creating
a function that wraps the operation and marks it immutable I don't
have a solution at
this time

​I can imagine that without a cast, depending on the way birthdate is
stored, it may behave differently to a cast index for ordering.

Geoff

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 02/26/2017 08:50 AM, Tom Lane wrote:

Geoff Winkless <pgsqladmin@geoff.dj> writes:

On 26 February 2017 at 16:09, Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

On 02/26/2017 07:56 AM, Geoff Winkless wrote:

On 26 February 2017 at 10:09, Sven R. Kunze <srkunze@mail.de
<mailto:srkunze@mail.de>>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@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Adrian Klaver <adrian.klaver@aklaver.com> 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.

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 02/26/2017 09:42 AM, Tom Lane wrote:

Adrian Klaver <adrian.klaver@aklaver.com> 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@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Hi Geoff, Adrian and Tom,

thanks for your responses so far. Excuse my late response. I will
respond to Tom's mail as it covers most points:

On 26.02.2017 17:50, Tom Lane wrote:

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 understand that timezone settings can have serious consequences when
parsing text to datetime.

My conceptual issue is that wrapping an "unsafe" operation up into a
function and **marking** it as "safe" is not making things safer.
Basically by-passing security guards.

So, what can I do to parse texts to date(times) in a safe manner?

I'd like to do it the right way. I can safely provide the timezone for
those dates but it won't be in the jsonb data.

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.

These were my reasons:

1) sanity checks (already noted)
2) index date ranges (using gist)
3) maybe performance (comparing texts vs comparing dates) but I couldn't
think of ways to test this

That's the current schema:
Table "public.docs"
Column | Type | Modifiers
--------+---------+---------------------------------------------------
id | integer | not null default nextval('docs_id_seq'::regclass)
meta | jsonb |
Indexes:
"docs_pkey" PRIMARY KEY, btree (id)
"docs_address_idx" gin (to_tsvector('english'::regconfig, meta ->>
'address'::text))
"docs_address_trgm_idx" gin ((meta ->> 'address'::text) gin_trgm_ops)
"docs_birthdate_idx" btree ((meta ->> 'birthdate'::text))
"docs_meta_idx" gin (meta jsonb_path_ops)
"docs_name_idx" gin (to_tsvector('english'::regconfig, meta ->>
'name'::text))

Thanks to the ISO date format, I got by with a btree index on birthdate
as Tom suggested.

The index supports queries like the following (although 22secs still is
not great on 10Mrows)

explain analyze select meta->>'birthdate' from docs where
meta->>'birthdate' > '2000-01-01' and meta->>'birthdate' < '2000-12-31'
order by meta->>'birthdate';
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------
Index Scan using docs_birthdate_idx on docs (cost=0.43..46067.43
rows=50000 width=136) (actual time=2.118..22177.710 rows=209955 loops=1)
Index Cond: (((meta ->> 'birthdate'::text) > '2000-01-01'::text) AND
((meta ->> 'birthdate'::text) < '2000-12-31'::text))
Planning time: 0.205 ms
Execution time: 22229.615 ms

Regard,
Sven

On 27 February 2017 at 10:52, Sven R. Kunze <srkunze@mail.de> wrote:

So, what can I do to parse texts to date(times) in a safe manner?

You know best the format of your data; if you know that your date field is
always in a particular style and timezone, you can write a function that
can be considered safe to set IMMUTABLE, where a more generic​ system
todate function cannot.

It might be sensible to call the function something that describes it
exactly, rather than my_to_date you could call it utc_yyyymmdd_todate or
something, just in case someone comes along later and sees an immutable
todate function and thinks they can use it for something else.

Geoff

On 27.02.2017 12:10, Geoff Winkless wrote:

On 27 February 2017 at 10:52, Sven R. Kunze <srkunze@mail.de
<mailto:srkunze@mail.de>>wrote:

So, what can I do to parse texts to date(times) in a safe manner?

You know best the format of your data; if you know that your date
field is always in a particular style and timezone, you can write a
function that can be considered safe to set IMMUTABLE, where a more
generic​ system todate function cannot.

It might be sensible to call the function something that describes it
exactly, rather than my_to_date you could call it utc_yyyymmdd_todate
or something, just in case someone comes along later and sees an
immutable todate function and thinks they can use it for something else.

Geoff

Thanks, Geoff. It's not the most convenient way to define an index to
define a function first and that all future queries need to use that
very function in order to utilize the index. Though, it's the most
safest and best documenting way.

So, I got two additional questions:

Why is this relevant for dates? I cannot see that dates are
timezone-influenced.

I still feel that a function is overkill for a simple text to date
conversion. Couldn't there be an IMMUTABLE modifier for an expression to
mark it as immutable?

"SELECT '2007-02-02'::date;" just works. It would be great if one could
define an index with the same ease. I already can see how our
application developers need constant reminders that "in case of dates,
use 'magic_function' first". If they don't, the application will suffer
from bad performance.

Thanks in advance for your replies.

Regards,
Sven

On 02/27/2017 07:03 AM, Sven R. Kunze wrote:

On 27.02.2017 12:10, Geoff Winkless wrote:

On 27 February 2017 at 10:52, Sven R. Kunze <srkunze@mail.de
<mailto:srkunze@mail.de>>wrote:

So, what can I do to parse texts to date(times) in a safe manner?

You know best the format of your data; if you know that your date
field is always in a particular style and timezone, you can write a
function that can be considered safe to set IMMUTABLE, where a more
generic​ system todate function cannot.

It might be sensible to call the function something that describes it
exactly, rather than my_to_date you could call it utc_yyyymmdd_todate
or something, just in case someone comes along later and sees an
immutable todate function and thinks they can use it for something else.

Geoff

Thanks, Geoff. It's not the most convenient way to define an index to
define a function first and that all future queries need to use that
very function in order to utilize the index. Though, it's the most
safest and best documenting way.

So, I got two additional questions:

Why is this relevant for dates? I cannot see that dates are
timezone-influenced.

Per Tom's post, see points 2 & 3:

"* 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 still feel that a function is overkill for a simple text to date
conversion. Couldn't there be an IMMUTABLE modifier for an expression to
mark it as immutable?

"SELECT '2007-02-02'::date;" just works. It would be great if one could
define an index with the same ease. I already can see how our
application developers need constant reminders that "in case of dates,
use 'magic_function' first". If they don't, the application will suffer
from bad performance.

Thanks in advance for your replies.

Regards,
Sven

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 27.02.2017 16:37, Adrian Klaver wrote:

On 02/27/2017 07:03 AM, Sven R. Kunze wrote:

Why is this relevant for dates? I cannot see that dates are
timezone-influenced.

Per Tom's post, see points 2 & 3:

Maybe, I am on a completely wrong track here, but to me dates still
don't look timezone dependent. They are just dates and not times, aren't
they?

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

Reading this through again, I got an idea:

Wouldn't it be possible to provide an immutable variant of to_timestamp
and to_date with a third parameter to specify the otherwise
setting-dependent timezone?

I still feel that a function is overkill for a simple text to date
conversion. Couldn't there be an IMMUTABLE modifier for an expression to
mark it as immutable?

Any thoughts on this?

"SELECT '2007-02-02'::date;" just works. It would be great if one could
define an index with the same ease. I already can see how our
application developers need constant reminders that "in case of dates,
use 'magic_function' first". If they don't, the application will suffer
from bad performance.

Best regards,
Sven

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 02/27/2017 09:08 AM, Sven R. Kunze wrote:

On 27.02.2017 16:37, Adrian Klaver wrote:

On 02/27/2017 07:03 AM, Sven R. Kunze wrote:

Why is this relevant for dates? I cannot see that dates are
timezone-influenced.

Per Tom's post, see points 2 & 3:

Maybe, I am on a completely wrong track here, but to me dates still
don't look timezone dependent. They are just dates and not times, aren't
they?

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'

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

Reading this through again, I got an idea:

Wouldn't it be possible to provide an immutable variant of to_timestamp
and to_date with a third parameter to specify the otherwise
setting-dependent timezone?

I still feel that a function is overkill for a simple text to date
conversion. Couldn't there be an IMMUTABLE modifier for an expression to
mark it as immutable?

Any thoughts on this?

"SELECT '2007-02-02'::date;" just works. It would be great if one could
define an index with the same ease. I already can see how our
application developers need constant reminders that "in case of dates,
use 'magic_function' first". If they don't, the application will suffer
from bad performance.

Best regards,
Sven

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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.

=# -- 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

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:

/messages/by-id/11190.1488127834@sss.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.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 28.02.2017 15:40, Adrian Klaver wrote:

[explanation of why date casting and to_datetime don't work]

Why is to_date not immutable?

Regards,
Sven

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 02/28/2017 07:30 AM, Sven R. Kunze wrote:

On 28.02.2017 15:40, Adrian Klaver wrote:

[explanation of why date casting and to_datetime don't work]

Why is to_date not immutable?

Not sure, but if I where to hazard a guess, from the source code in
formatting.c:

https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/backend/utils/adt/formatting.c;h=e552c8d20b61a082049068d2f8d776e35fef1179;hb=HEAD

to_date(PG_FUNCTION_ARGS)
{
text *date_txt = PG_GETARG_TEXT_P(0);
text *fmt = PG_GETARG_TEXT_P(1);
DateADT result;
struct pg_tm tm;
fsec_t fsec;

do_to_timestamp(date_txt, fmt, &tm, &fsec);
....

/*
* do_to_timestamp: shared code for to_timestamp and to_date

The shared code makes it not immutable:

test=> select * from pg_proc where proname ilike 'to_date';
...

provolatile | s

....

https://www.postgresql.org/docs/9.6/static/catalog-pg-proc.html

provolatile char

provolatile tells whether the function's result depends only on its
input arguments, or is affected by outside factors. It is i for
"immutable" functions, which always deliver the same result for the same
inputs. It is s for "stable" functions, whose results (for fixed inputs)
do not change within a scan. It is v for "volatile" functions, whose
results might change at any time. (Use v also for functions with
side-effects, so that calls to them cannot get optimized away.)

Regards,
Sven

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Tue, Feb 28, 2017 at 8:59 AM, Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

On 02/28/2017 07:30 AM, Sven R. Kunze wrote:

On 28.02.2017 15:40, Adrian Klaver wrote:

[explanation of why date casting and to_datetime don't work]

Why is to_date not immutable?

Not sure, but if I where to hazard a guess, from the source code in
formatting.c:

https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;
f=src/backend/utils/adt/formatting.c;h=e552c8d20b61a08204906
8d2f8d776e35fef1179;hb=HEAD

to_date(PG_FUNCTION_ARGS)
{
text *date_txt = PG_GETARG_TEXT_P(0);
text *fmt = PG_GETARG_TEXT_P(1);
DateADT result;
struct pg_tm tm;
fsec_t fsec;

do_to_timestamp(date_txt, fmt, &tm, &fsec);
....

/*
* do_to_timestamp: shared code for to_timestamp and to_date

The shared code makes it not immutable:

​Further on that reads:

"​* Parse the 'date_txt' according to 'fmt', return results as a struct
pg_tm
* and fractional seconds."

Which makes it sound like a pure text parsing routine that applies minimal
logic to the values that it is parsing. In fact, its doesn't even accept a
TZ/OF formatting codes that could be used to determine shift. to_date is
always to going to output a date value that reflects the literal input text
with "positions" determined by the input format code.

Per Tom Lane [1] while the current implementation is in fact immutable at
some point in the future we may wish to add additional environmental logic
which will require that it be marked STABLE.

1. /messages/by-id/4177.1322537348@sss.pgh.pa.us

I would be considering a trigger that populates a date column and a normal
index on said date column.

David J.