Can't index Noda Time types

[Leh2]

Indexing Node Time types fails with

functions in index expression must be marked IMMUTABLE

Index example: CREATE INDEX mt_doc_targetwithdates_idx_local_date ON noda_time_acceptance.mt_doc_targetwithdates USING btree ((CAST(data ->> 'LocalDate' as date)));

Added failing test here #2413

[mysticmind]

I think it is going to be a bit tricky to deal with. This will require a wrapper function for each of the mapped NodaTime Postgres types which does the cast and is marked immutable to circumvent functions in index expression must be marked IMMUTABLE issue:

CREATE
OR REPLACE FUNCTION mt_to_date_immutable(data varchar) RETURNS date AS
$$
BEGIN
return CAST(data as date);
END;
$$
LANGUAGE plpgsql immutable;

and then while creating index, you will use the above function instead of the cast as below:

CREATE INDEX mt_doc_targetwithdates_idx_local_date ON noda_time_acceptance.mt_doc_targetwithdates 
USING btree (mt_to_date_immutable(data ->> 'LocalDate'));

Possibly an alternative and easier approach is to create a duplicate field with relevant type and create an index on it. This won't require any changes on Marten as such. You could try this and confirm. If this works well then we will add docs for the same.

[Leh2]

Creating a duplicated field works, but if NodaTime is the preferred way of working with date and time (according to Npgsql) I think this should be supported.

With duplicated field you also get a potential unused index if you need a multicolumn index.