Postgres Pro
Downloads
Home   >   mailing lists

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

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

From
Ken Tanzer
Date:
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.

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

From
Adrian Klaver
Date:
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.tanzer@agency-software.org <mailto:ken.tanzer@agency-software.org>
> (253) 245-3801
> 
> Subscribe to the mailing list 
> <mailto:agency-general-request@lists.sourceforge.net?body=subscribe> to
> learn more about AGENCY or
> follow the discussion.


-- 
Adrian Klaver
adrian.klaver@aklaver.com

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

From
Ken Tanzer
Date:
On Fri, Jan 4, 2019 at 2:27 PM Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 1/4/19 2:21 PM, Ken Tanzer wrote:
> 
> 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?


Here's a few of the ones I tried:
ag_reach=> CREATE INDEX ON export_hch_encounter_history (CAST("Service_Date" AS date));
ERROR:  functions in index expression must be marked IMMUTABLE
ag_reach=> CREATE INDEX ON export_hch_encounter_history (CAST(LEFT("Service_Date",10) AS date));
ERROR:  functions in index expression must be marked IMMUTABLE
ag_reach=> CREATE INDEX ON export_hch_encounter_history (CAST("Service_Date"::timestamp AS date));
ERROR:  functions in index expression must be marked IMMUTABLE
ag_reach=> CREATE INDEX ON export_hch_encounter_history (("Service_Date"::date));
ERROR:  functions in index expression must be marked IMMUTABLE
ag_reach=> CREATE INDEX ON export_hch_encounter_history ((LEFT("Service_Date",10)::date));
ERROR:  functions in index expression must be marked IMMUTABLE


--
AGENCY Software  
A Free Software data system
By and for non-profits
(253) 245-3801

learn more about AGENCY or
follow the discussion.

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

From
Andrew Gierth
Date:
>>>>> "Ken" == Ken Tanzer <ken.tanzer@gmail.com> writes:

 Ken> Hi. I've got a text field in a table that holds this style of
 Ken> timestamp:

 Ken> 2014-10-23T00:00:00

You can't make this a field of type "timestamp" rather than text?

Casts from text to either date or timestamp are mutable because they
depend on the current DateStyle value (_you_ might know that your values
are always ISO format, but the code doesn't). You can't even avoid this
with to_date or to_timestamp and a fixed format, because those functions
are also mutable since some of the format options are locale-dependent
(again, you might know that you're not using those, but the code
doesn't).

If the column type were timestamp, though, then you could do comparisons
directly, or cast the value to "date" (which is an immutable cast).

If you absolutely can't change the column type, then one option would be
to do your own fixed-format date parsing function (and label it
immutable), e.g.

create function iso_timestamp(text)
  returns timestamp without time zone
  as $$ select case when $1 ~ '^\d\d\d\d-?\d\d-?\d\dT\d\d:?\d\d:?\d\d$'
                    then $1::timestamp
                    else null end $$
  set DateStyle = 'ISO,YMD'
  language sql immutable strict;

or

create function iso_date(text)
  returns date
  as $$ select case when $1 ~ '^\d\d\d\d-?\d\d-?\d\d(?![^T])'
                    then substring($1 from '^\d\d\d\d-?\d\d-?\d\d')::date
                    else null end $$
  set DateStyle = 'ISO,YMD'
  language sql immutable strict;

-- 
Andrew (irc:RhodiumToad)

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

From
Ken Tanzer
Date:
On Fri, Jan 4, 2019 at 2:54 PM Andrew Gierth <andrew@tao11.riddles.org.uk> wrote:
>>>>> "Ken" == Ken Tanzer <ken.tanzer@gmail.com> writes:

 Ken> Hi. I've got a text field in a table that holds this style of
 Ken> timestamp:

 Ken> 2014-10-23T00:00:00

You can't make this a field of type "timestamp" rather than text?


 I actually can't, or rather don't want to.  The underlying data this is drawn from is actually a date field, but this particular table keeps a history of what we actually transmitted to another organization, and I want to keep it as an exact replication of what we sent.

 
If you absolutely can't change the column type, then one option would be
to do your own fixed-format date parsing function (and label it
immutable), e.g.

create function iso_timestamp(text)
  returns timestamp without time zone
  as $$ select case when $1 ~ '^\d\d\d\d-?\d\d-?\d\dT\d\d:?\d\d:?\d\d$'
                    then $1::timestamp
                    else null end $$
  set DateStyle = 'ISO,YMD'
  language sql immutable strict;

or

create function iso_date(text)
  returns date
  as $$ select case when $1 ~ '^\d\d\d\d-?\d\d-?\d\d(?![^T])'
                    then substring($1 from '^\d\d\d\d-?\d\d-?\d\d')::date
                    else null end $$
  set DateStyle = 'ISO,YMD'
  language sql immutable strict;


Yeah, I thought I might have to do that, but when I create that index it still doesn't seem to use the index for queries.  I also found the (immutable) make_date function, with the same problem.  I can see why the planner wouldn't know how to use them:

CREATE INDEX ON export_hch_encounter_history ( iso_date("Service_Date"));
CREATE INDEX ON export_hch_encounter_history ( make_date(LEFT("Service_Date",4)::int,SUBSTRING("Service_Date",6,2)::int,SUBSTRING("Service_Date",9,2)::int));
EXPLAIN ANALYZE SELECT * FROM export_hch_encounter_history WHERE "Service_Date"::date BETWEEN '2018-01-01'::date AND '2018-12-31'::date;
                                                              QUERY PLAN                                                              
--------------------------------------------------------------------------------------------------------------------------------------Seq Scan on export_hch_encounter_history  (cost=0.00..19458.53 rows=885 width=656) (actual time=117.246..253.583 rows=26548 loops=1)  Filter: ((("Service_Date")::date >= '2018-01-01'::date) AND (("Service_Date")::date <= '2018-12-31'::date))  Rows Removed by Filter: 150393Planning time: 0.401 msExecution time: 284.036 ms
(5 rows)
As opposed to casting the WHERE clause parameters:

EXPLAIN ANALYZE SELECT * FROM export_hch_encounter_history WHERE "Service_Date" BETWEEN '2018-01-01'::text AND '2018-12-31'::text;                                                                            QUERY PLAN                                                                            
-------------------------------------------------------------------------------------------------------------------------------------------------------
-------------Bitmap Heap Scan on export_hch_encounter_history  (cost=799.91..16639.67 rows=26487 width=656) (actual time=15.611..51.309 rows=26548 loops=1)  Recheck Cond: (("Service_Date" >= '2018-01-01'::text) AND ("Service_Date" <= '2018-12-31'::text))  Heap Blocks: exact=2432  ->  Bitmap Index Scan on "export_hch_encounter_history_Service_Date_idx"  (cost=0.00..793.29 rows=26487 width=0) (actual time=15.250..15.252 rows=26
548 loops=1)        Index Cond: (("Service_Date" >= '2018-01-01'::text) AND ("Service_Date" <= '2018-12-31'::text))Planning time: 0.739 msExecution time: 80.523 ms
(7 rows)

Thanks for your help and response!

Ken

--
AGENCY Software  
A Free Software data system
By and for non-profits
(253) 245-3801

learn more about AGENCY or
follow the discussion.

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

From
Kevin Brannen
Date:

From: Ken Tanzer <ken.tanzer@gmail.com>

On Fri, Jan 4, 2019 at 2:54 PM Andrew Gierth <andrew@tao11.riddles.org.uk> wrote:

>>>>> "Ken" == Ken Tanzer <ken.tanzer@gmail.com> writes:

 Ken> Hi. I've got a text field in a table that holds this style of
 Ken> timestamp:

 Ken> 2014-10-23T00:00:00

You can't make this a field of type "timestamp" rather than text?

 

> I actually can't, or rather don't want to.  The underlying data this is drawn from is actually a date field, but this particular table keeps a history of what we actually transmitted to another organization, and I want to keep it as an exact replication of what we sent.

 

 

If it’s not too painful, add another column to your table of type DATE, and on INSERT shove your “timestamp” into that, converting/casting as needed, then index that. So at the cost of an extra 4 bytes per row, you can have both your “transmission” value and an indexable value.

 

Kevin

This e-mail transmission, and any documents, files or previous e-mail messages attached to it, may contain confidential information. If you are not the intended recipient, or a person responsible for delivering it to the intended recipient, you are hereby notified that any disclosure, distribution, review, copy or use of any of the information contained in or attached to this message is STRICTLY PROHIBITED. If you have received this transmission in error, please immediately notify us by reply e-mail, and destroy the original transmission and its attachments without reading them or saving them to disk. Thank you.

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

From
Andrew Gierth
Date:
>>>>> "Ken" == Ken Tanzer <ken.tanzer@gmail.com> writes:

 >> If you absolutely can't change the column type, then one option
 >> would be to do your own fixed-format date parsing function (and
 >> label it immutable), e.g.
 >> 
 >> create function iso_timestamp(text)
 >> returns timestamp without time zone
 >> as $$ select case when $1 ~ '^\d\d\d\d-?\d\d-?\d\dT\d\d:?\d\d:?\d\d$'
 >> then $1::timestamp
 >> else null end $$
 >> set DateStyle = 'ISO,YMD'
 >> language sql immutable strict;
 >> 
 >> or
 >> 
 >> create function iso_date(text)
 >> returns date
 >> as $$ select case when $1 ~ '^\d\d\d\d-?\d\d-?\d\d(?![^T])'
 >> then substring($1 from '^\d\d\d\d-?\d\d-?\d\d')::date
 >> else null end $$
 >> set DateStyle = 'ISO,YMD'
 >> language sql immutable strict;

 Ken> Yeah, I thought I might have to do that, but when I create that
 Ken> index it still doesn't seem to use the index for queries.

It won't use the index unless you use the same function in the query
too.

i.e.

CREATE INDEX ON ... (iso_date("Service_Date"));

SELECT * FROM ... WHERE iso_date("Service_Date") BETWEEN ... AND ...;

-- 
Andrew (irc:RhodiumToad)