generated column cast from timestamptz to timestamp not OK.

From: alias <postgres(dot)rocks(at)gmail(dot)com>
To: pgsql-general <pgsql-general(at)lists(dot)postgresql(dot)org>
Subject: generated column cast from timestamptz to timestamp not OK.
Date: 2022-05-13 10:47:00
Message-ID: CAJA4AWRedm=hZAve7gU9OyPKM_9Jx30i7Mi7n5-Sc5bu9JX1OQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

CREATE TABLE test_g (
a timestamptz,
b timestamp GENERATED ALWAYS AS (a::timestamp) STORED
);
then an error occurred.

> ERROR: 42P17: generation expression is not immutable
> LOCATION: cookDefault, heap.c:2768
>

However the following 2 commands is ok.

CREATE TABLE test_i (
> a int,
> b bigint GENERATED ALWAYS AS (a::bigint) STORED
> );
>

> CREATE TABLE test_c (
> a varchar,
> b text GENERATED ALWAYS AS (a::text) STORED
> );
>

I didn't get it. timestamptz changes then timestamp also changes. timestamp
is part of timestamptz...
Even if column timestamptz is some value that is constantly changing (like
now() ), it changes/updates then just in the mean time captures timestamp
to column b.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Francisco Olarte 2022-05-13 11:45:23 Re: generated column cast from timestamptz to timestamp not OK.
Previous Message Bryn Llewellyn 2022-05-13 06:43:51 Re: Deferred constraint trigger semantics