Re: date_trunc not immutable

On 2018-12-15, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> wrote:
> On 12/15/18 3:26 PM, Ravi Krishna wrote:
>> Version: PG 10.6 on AWS Linux.
>>
>> I am trying to create an index on function date_trunc('month',timestamp)
>>
>> PG is complaining that the function must be marked as IMMUTABLE. So I
>> assume that date_trunc is not marked as immutable.
>>
>> Definition of immutable from PG documentation
>> ====================================
>>
>> All functions and operators used in an index definition must be
>> "immutable", that is, their results must depend only on their arguments
>> and never on any outside influence (such as the contents of another
>> table or the current time). This restriction ensures that the behavior
>> of the index is well-defined. To use a user-defined function in an index
>> expression or WHERE clause, remember to mark the function immutable when
>> you create it.
>> ===================================
>> What am I missing? date_trunc will always return the same value for a
>> given value. Not sure how I can mark a PG function as immutable.
>
> No it won't:
>
> show timezone;
> TimeZone
> ------------
> US/Pacific
>
> select date_trunc('hour', now());
> date_trunc
> ------------------------
> 2018-12-15 15:00:00-08
>
> set timezone='UTC';
>
> select date_trunc('hour', now());
>
> date_trunc
> ------------------------
> 2018-12-15 23:00:00+00
>

Ravi, the date_trunc('month',timestamp) is already immutable (at least in PG11):
postgres=# \df+ date_trunc
Schema | Name | Result data type |
Argument data types | Volatility | ...
------------+------------+-----------------------------+-----------------------------------+------------+-...
pg_catalog | date_trunc | timestamp without time zone | text,
timestamp without time zone | immutable | ...

For the "date_trunc(text, timestampTZ) see Adrian's response, why it
does not always return the same values for the same input.

--
Best regards,
Vitaly Burovoy