Обсуждение: Immutable way to cast timestamp TEXT to DATE? (for index)
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.
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.
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.