From: | Олег Самойлов <splarv(at)ya(dot)ru> |
---|---|
To: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: is date_part immutable or not? |
Date: | 2020-08-21 12:05:48 |
Message-ID: | 25DC3683-C176-409E-A4E6-92034BBA341F@ya.ru |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
My mistake.
Schema | pg_catalog
Name | date_part
Result data type | double precision
Argument data types | text, timestamp with time zone
Type | func
Volatility | stable
Parallel | safe
Owner | postgres
Security | invoker
Access privileges |
Language | internal
Source code | timestamptz_part
Description | extract field from timestamp with time zone
is stable, but
Schema | pg_catalog
Name | date_part
Result data type | double precision
Argument data types | text, date
Type | func
Volatility | immutable
Parallel | safe
Owner | postgres
Security | invoker
Access privileges |
Language | sql
Source code | select pg_catalog.date_part($1, cast($2 as timestamp without tim
e zone))
Description | extract field from date
is immutable and
Schema | pg_catalog
Name | date_part
Result data type | double precision
Argument data types | text, timestamp without time zone
Type | func
Volatility | immutable
Parallel | safe
Owner | postgres
Security | invoker
Access privileges |
Language | internal
Source code | timestamp_part
Description | extract field from timestamp
immutable too. But date_part('isodow', log_time::date) and date_part('isodow', log_time::timestamp) don't work too.
How to workaround this?
> 21 авг. 2020 г., в 14:57, Олег Самойлов <splarv(at)ya(dot)ru> написал(а):
>
> PostgreSQL 12.4
> Just create partitioned table for PostgreSQL logs
>
> CREATE TABLE pglog.pglog (
> log_time timestamp(3) with time zone,
> user_name text,
> database_name text,
> process_id integer,
> connection_from text,
> session_id text,
> session_line_num bigint,
> command_tag text,
> session_start_time timestamp with time zone,
> virtual_transaction_id text,
> transaction_id bigint,
> error_severity text,
> sql_state_code text,
> message text,
> detail text,
> hint text,
> internal_query text,
> internal_query_pos integer,
> context text,
> query text,
> query_pos integer,
> location text,
> application_name text
> ) PARTITION BY LIST (date_part('isodow', log_time));
>
> ERROR: functions in partition key expression must be marked IMMUTABLE
>
> But, date_part is immutable
>
> Schema | pg_catalog
> Name | date_part
> Result data type | double precision
> Argument data types | text, time with time zone
> Type | func
> Volatility | immutable
> Parallel | safe
> Owner | postgres
> Security | invoker
> Access privileges |
> Language | internal
> Source code | timetz_part
> Description | extract field from time with time zone
>
> What is wrong here?
>
>
From | Date | Subject | |
---|---|---|---|
Next Message | iulian dragos | 2020-08-21 12:35:29 | Query plan prefers hash join when nested loop is much faster |
Previous Message | Thomas Boussekey | 2020-08-21 12:00:00 | When are largobject records TOASTed into pg_toast_2613? |