Postgres Pro
Downloads
Home   >   mailing lists

Immutable way to cast timestamp TEXT to DATE? (for index) - Mailing list pgsql-general

From Ken Tanzer
Subject Immutable way to cast timestamp TEXT to DATE? (for index)
Date
Msg-id CAD3a31XupySutj+RGLxQrOqji5gBZo6Z05rAYgRDRH10wi4Mag@mail.gmail.com
Whole thread Raw
Responses Re: Immutable way to cast timestamp TEXT to DATE? (for index)  (Adrian Klaver <adrian.klaver@aklaver.com>)
Re: Immutable way to cast timestamp TEXT to DATE? (for index)  (Andrew Gierth <andrew@tao11.riddles.org.uk>)
List pgsql-general
Tree view
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."

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
(253) 245-3801

learn more about AGENCY or
follow the discussion.

pgsql-general by date:

Previous
From: Rich Shepard
Date:
Subject: Re: Comparing dates in DDL
Next
From: Adrian Klaver
Date:
Subject: Re: Immutable way to cast timestamp TEXT to DATE? (for index)