Re: Immutable way to cast timestamp TEXT to DATE? (for index)

On 1/4/19 2:21 PM, Ken Tanzer wrote:
> Hi.  I've got a text field in a table that holds this style of timestamp:
>
> 2014-10-23T00:00:00
>
> I'd like to be able to create an index on the date portion of this field
> (as a date), because I have lots of queries that are searching for
> particular dates or ranges.
>
> I've tried various ways of getting to a date (::date, LEFT(x,10)::date,
> etc.), but all of them throw the error "functions in index expression
> must be marked IMMUTABLE."

?:
'2014-10-23T00:00:00'::timestamp

Can you also show the actual index expression?

>
> Is there any way, hacky or otherwise, people know of to do this?  Thanks
> in advance.
>
> Ken
>
>
> --
> AGENCY Software
> A Free Software data system
> By and for non-profits
> /http://agency-software.org//
> /https://demo.agency-software.org/client/
> ken(dot)tanzer(at)agency-software(dot)org <mailto:ken(dot)tanzer(at)agency-software(dot)org>
> (253) 245-3801
>
> Subscribe to the mailing list
> <mailto:agency-general-request(at)lists(dot)sourceforge(dot)net?body=subscribe> to
> learn more about AGENCY or
> follow the discussion.

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com