Re: [GENERAL] ERROR: functions in index expression must be marked IMMUTABLE

2017-03-03 Thread Geoff Winkless
On 3 March 2017 at 12:17, Sven R. Kunze wrote: > On 03.03.2017 11:43, Geoff Winkless wrote: > > ​One alternative would be to make to_date accept all language variants of > months simultaneously. A quick search of google suggests that there aren't > any overlaps between languages (ie where one lan

Re: [GENERAL] ERROR: functions in index expression must be marked IMMUTABLE

2017-03-03 Thread Sven R. Kunze
On 03.03.2017 11:43, Geoff Winkless wrote: ​One alternative would be to make to_date accept all language variants of months simultaneously. A quick search of google suggests that there aren't any overlaps between languages (ie where one language uses "Foo" for March and another uses "Foo" for M

Re: [GENERAL] ERROR: functions in index expression must be marked IMMUTABLE

2017-03-03 Thread Sven R. Kunze
On 03.03.2017 11:43, Geoff Winkless wrote: One alternative would be to make to_date accept all language variants of months simultaneously. A quick search of google suggests that there aren't any overlaps between languages (ie where one language uses "Foo" for March and another uses "Foo" for Ma

Re: [GENERAL] ERROR: functions in index expression must be marked IMMUTABLE

2017-03-03 Thread Geoff Winkless
On 1 March 2017 at 14:23, Sven R. Kunze wrote: > I don't consider rolling an UDF the best alternative especially after > having looked through many solution proposals on the Web which just take an > mutable expression and wrap them up in an immutable function. > ​One alternative would be to make

Re: [GENERAL] ERROR: functions in index expression must be marked IMMUTABLE

2017-03-01 Thread Sven R. Kunze
On 01.03.2017 14:40, Geoff Winkless wrote: On 1 March 2017 at 13:36, Sven R. Kunze >wrote: On 28.02.2017 17:50, David G. Johnston wrote: Supposedly one could provide a version of to_date that accepts a locale in which to interpret names in the input data - or

Re: [GENERAL] ERROR: functions in index expression must be marked IMMUTABLE

2017-03-01 Thread Geoff Winkless
On 1 March 2017 at 13:36, Sven R. Kunze wrote: > On 28.02.2017 17:50, David G. Johnston wrote: > > Supposedly one could provide a version of to_date that accepts a locale in > which to interpret names in the input data - or extend the format string > with some kind of "{locale=en_US}" syntax to a

Re: [GENERAL] ERROR: functions in index expression must be marked IMMUTABLE

2017-03-01 Thread Sven R. Kunze
On 28.02.2017 17:50, David G. Johnston wrote: ​That would seem to be it. cache_locale_time() at the top of DCH_to_char which is in the call stack of the shared parsing code for both to_date and to_timestamp. ​https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/backend/utils/adt/f

Re: [GENERAL] ERROR: functions in index expression must be marked IMMUTABLE

2017-02-28 Thread David G. Johnston
On Tue, Feb 28, 2017 at 9:35 AM, Geoff Winkless wrote: > On 28 February 2017 at 15:59, Adrian Klaver > 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

Re: [GENERAL] ERROR: functions in index expression must be marked IMMUTABLE

2017-02-28 Thread Geoff Winkless
On 28 February 2017 at 15:59, Adrian Klaver 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

Re: [GENERAL] ERROR: functions in index expression must be marked IMMUTABLE

2017-02-28 Thread David G. Johnston
On Tue, Feb 28, 2017 at 8:59 AM, Adrian Klaver 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

Re: [GENERAL] ERROR: functions in index expression must be marked IMMUTABLE

2017-02-28 Thread Adrian Klaver
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/gi

Re: [GENERAL] ERROR: functions in index expression must be marked IMMUTABLE

2017-02-28 Thread Sven R. Kunze
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/p

Re: [GENERAL] ERROR: functions in index expression must be marked IMMUTABLE

2017-02-28 Thread Adrian Klaver
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 unders

Re: [GENERAL] ERROR: functions in index expression must be marked IMMUTABLE

2017-02-28 Thread Sven R. Kunze
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 dates

Re: [GENERAL] ERROR: functions in index expression must be marked IMMUTABLE

2017-02-27 Thread Adrian Klaver
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

Re: [GENERAL] ERROR: functions in index expression must be marked IMMUTABLE

2017-02-27 Thread Sven R. Kunze
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 d

Re: [GENERAL] ERROR: functions in index expression must be marked IMMUTABLE

2017-02-27 Thread Adrian Klaver
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 mailto:srku...@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 you

Re: [GENERAL] ERROR: functions in index expression must be marked IMMUTABLE

2017-02-27 Thread Sven R. Kunze
On 27.02.2017 12:10, Geoff Winkless wrote: On 27 February 2017 at 10:52, Sven R. Kunze >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

Re: [GENERAL] ERROR: functions in index expression must be marked IMMUTABLE

2017-02-27 Thread Geoff Winkless
On 27 February 2017 at 10:52, Sven R. Kunze 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 t

Re: [GENERAL] ERROR: functions in index expression must be marked IMMUTABLE

2017-02-27 Thread Sven R. Kunze
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 o

Re: [GENERAL] ERROR: functions in index expression must be marked IMMUTABLE

2017-02-26 Thread Adrian Klaver
On 02/26/2017 09:42 AM, Tom Lane wrote: Adrian Klaver 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 '-MM-DD', then a regular textual index would s

Re: [GENERAL] ERROR: functions in index expression must be marked IMMUTABLE

2017-02-26 Thread Tom Lane
Adrian Klaver 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 '-MM-DD', then a regular textual index would sort >> the same anyway. Perhaps

Re: [GENERAL] ERROR: functions in index expression must be marked IMMUTABLE

2017-02-26 Thread Adrian Klaver
On 02/26/2017 08:50 AM, Tom Lane wrote: Geoff Winkless writes: On 26 February 2017 at 16:09, Adrian Klaver wrote: On 02/26/2017 07:56 AM, Geoff Winkless wrote: On 26 February 2017 at 10:09, Sven R. Kunze mailto:srku...@mail.de>>wrote: # create index docs_birthdate_idx ON docs using btree ((

Re: [GENERAL] ERROR: functions in index expression must be marked IMMUTABLE

2017-02-26 Thread Adrian Klaver
On 02/26/2017 08:15 AM, Geoff Winkless wrote: On 26 February 2017 at 16:09, Adrian Klaver mailto:adrian.kla...@aklaver.com>>wrote: On 02/26/2017 07:56 AM, Geoff Winkless wrote: > On 26 February 2017 at 10:09, Sven R. Kunze mailto:srku...@mail.de> >

Re: [GENERAL] ERROR: functions in index expression must be marked IMMUTABLE

2017-02-26 Thread Tom Lane
Geoff Winkless writes: > On 26 February 2017 at 16:09, Adrian Klaver > wrote: >> On 02/26/2017 07:56 AM, Geoff Winkless wrote: >>> On 26 February 2017 at 10:09, Sven R. Kunze >> >wrote: # create index docs_birthdate_idx ON docs using btree (((meta->>'birthdate'):

Re: [GENERAL] ERROR: functions in index expression must be marked IMMUTABLE

2017-02-26 Thread Geoff Winkless
On 26 February 2017 at 16:09, Adrian Klaver wrote: > On 02/26/2017 07:56 AM, Geoff Winkless wrote: > > On 26 February 2017 at 10:09, Sven R. Kunze > >wrote: > > > > >>># create index docs_birthdate_idx ON docs using btree > > (((meta->>'birthdate')::date)); > >

Re: [GENERAL] ERROR: functions in index expression must be marked IMMUTABLE

2017-02-26 Thread Adrian Klaver
On 02/26/2017 07:56 AM, Geoff Winkless wrote: > On 26 February 2017 at 10:09, Sven R. Kunze >wrote: > > >>># create index docs_birthdate_idx ON docs using btree > (((meta->>'birthdate')::date)); > ERROR: functions in index expression must be marked IMMUTABLE >

Re: [GENERAL] ERROR: functions in index expression must be marked IMMUTABLE

2017-02-26 Thread Geoff Winkless
On 26 February 2017 at 10:09, Sven R. Kunze 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 becaus

[GENERAL] ERROR: functions in index expression must be marked IMMUTABLE

2017-02-26 Thread Sven R. Kunze
Hello everybody, I'd like to implement a btree date index from json input data. >>># \d docs Table "public.docs" Column | Type | Modifiers +-+--- id | integer | not null default nextval('docs_id_seq