Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1qCQgd-0003tN-1N for pgsql-admin@arkaria.postgresql.org; Thu, 22 Jun 2023 20:13:47 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1qCQgb-0002FK-Or for pgsql-admin@arkaria.postgresql.org; Thu, 22 Jun 2023 20:13:45 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1qCQgb-0002FB-8o for pgsql-admin@lists.postgresql.org; Thu, 22 Jun 2023 20:13:45 +0000 Received: from mail-lf1-x133.google.com ([2a00:1450:4864:20::133]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1qCQgZ-0044xc-1Y for pgsql-admin@lists.postgresql.org; Thu, 22 Jun 2023 20:13:44 +0000 Received: by mail-lf1-x133.google.com with SMTP id 2adb3069b0e04-4f122ff663eso10578798e87.2 for ; Thu, 22 Jun 2023 13:13:42 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=dataegret-com.20221208.gappssmtp.com; s=20221208; t=1687464821; x=1690056821; h=to:in-reply-to:cc:references:message-id:date:subject:mime-version :from:content-transfer-encoding:from:to:cc:subject:date:message-id :reply-to; bh=5Euwe96tQU3AvRB7nxE0WhfZ84dBYWt0w/zK1UW8jmQ=; b=HH5VSkokFeNlBsYuBKISYmuVHeCVphilECLAMOr3hQHbfSYTGSm62Lzm4lBWnssdrk ijsngpp/MYDtfsprkKxOxbuZy95dA8mtzbRFj11/yEsIjuupnSaGIiastDHVxanrW4Yd PTmPa7mTh/4SrzRhUcX1V4ngi/gbBf5WR4a5UnKhWRR6DJYPY+E/rvxlhl2uU4awAjyN f1KxEQB+BFT8Q711SDuacJjZGXtfgQQxcVjqNSDbLr9zlYJkWyiKlZQVoUMc07pBUyw1 TdsHa/rjzCdAIJeJFN2j6lG9jqAB8FNYqqtlxzbowoq9bqi89KGMl/CTs4yJTdgxKpP0 mBcg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20221208; t=1687464821; x=1690056821; h=to:in-reply-to:cc:references:message-id:date:subject:mime-version :from:content-transfer-encoding:x-gm-message-state:from:to:cc :subject:date:message-id:reply-to; bh=5Euwe96tQU3AvRB7nxE0WhfZ84dBYWt0w/zK1UW8jmQ=; b=Y9pxI775oe03QwOHb2IJiqmI3fIQm2ZFGQtWeGsLtEJ1wQWcmYtoCS61UOLwUAmu9h nGI6LE3G1ySrtYHEcV+JjjFy0NjeOKIRG2FSdNBYSs2BkCb5KG4rn0jR5kvwh+5GS+t/ nW5QapCBv+z1GeEOQPto7Atyv0omve5XRFZWCfsCU9xZWY1J+YjzZcApJB7eQ60wVkcL V2BwSKDHtA0ZhZ+kQe9wmL1Kezw15IjG3m7MSQVBst0rFiFGIZll29tCaMeh+uL0egSh 41EVAARuYVhaCwr8SGp6VjmYdJ+e47JG04+OTJVxOUojwS0EYgcTTTOrzWEJYUzTggeg AwJg== X-Gm-Message-State: AC+VfDzkWpdCklZzaw2xNGiLtoOI7sykrGGywj00vvp/P2menSK/P6px EY+6yNry6a0/TbmD9ShLwySJlknv9QemNqN2Nh4E X-Google-Smtp-Source: ACHHUZ7E+vkEo4JaakDx9MNr/gZJu+3Xixvm7gR/CukhSzrjRAhaz5awziMZrutv3wRYJ1PLpR2ouA== X-Received: by 2002:a05:6512:471:b0:4f8:578f:acf1 with SMTP id x17-20020a056512047100b004f8578facf1mr10993788lfd.46.1687464821454; Thu, 22 Jun 2023 13:13:41 -0700 (PDT) Received: from smtpclient.apple ([145.224.74.191]) by smtp.gmail.com with ESMTPSA id m17-20020a7bce11000000b003f8140763c7sm373156wmc.30.2023.06.22.13.13.40 (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Thu, 22 Jun 2023 13:13:40 -0700 (PDT) Content-Type: multipart/alternative; boundary=Apple-Mail-E123EC61-3932-4E5E-89B7-856359FF1987 Content-Transfer-Encoding: 7bit From: Ilya Kosmodemiansky Mime-Version: 1.0 (1.0) Subject: Re: GENERATE AS Date: Thu, 22 Jun 2023 22:13:28 +0200 Message-Id: References: <7c48174f8ce449af8f3e875bdeca7150@express-scripts.com> Cc: pgsql-admin@lists.postgresql.org In-Reply-To: <7c48174f8ce449af8f3e875bdeca7150@express-scripts.com> To: "Wetmore, Matthew (CTR)" X-Mailer: iPhone Mail (20F66) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --Apple-Mail-E123EC61-3932-4E5E-89B7-856359FF1987 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: quoted-printable Hi Matthew, > On 22. Jun 2023, at 22:00, Wetmore, Matthew (CTR) wrote: > 4. ALTER TABLE auto_auth.matt > ADD column matt_hour INT GENERATED ALWAYS AS (date_part('h= our', current_timestamp)::INT - date_part('hour', matt_time)::INT) stored = =20 > =20 > ERROR: generation expression is not immutable > SQL state: 42P17 You can use only immutable functions to generate columns. Timestamp with tim= e zone as an argument makes function not immutable because timezone could be= set locally and results could differ depending on timezone=20 best regards, Ilya > =20 > =20 --Apple-Mail-E123EC61-3932-4E5E-89B7-856359FF1987 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 7bit
Hi Matthew,

On 22. Jun 2023, at 22:00, Wetmore, Matthew (CTR) <Matthew.Wetmore@express-scripts.com> wrote:

4. ALTER TABLE auto_auth.matt

               ADD column matt_hour INT  GENERATED ALWAYS AS (date_part('hour', current_timestamp)::INT - date_part('hour', matt_time)::INT) stored       

              

               ERROR:  generation expression is not immutable

SQL state: 42P17


You can use only immutable functions to generate columns. Timestamp with time zone as an argument makes function not immutable because timezone could be set locally and  results could differ depending on timezone 

best regards,
Ilya

 

 

--Apple-Mail-E123EC61-3932-4E5E-89B7-856359FF1987--