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 1pPJNS-0006X0-6c for pgsql-general@arkaria.postgresql.org; Tue, 07 Feb 2023 08:30:58 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pPJNQ-0001gp-HJ for pgsql-general@arkaria.postgresql.org; Tue, 07 Feb 2023 08:30:56 +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 1pPJNQ-0001gW-1Q for pgsql-general@lists.postgresql.org; Tue, 07 Feb 2023 08:30:56 +0000 Received: from mail-wr1-x436.google.com ([2a00:1450:4864:20::436]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1pPJNL-0006cv-HX for pgsql-general@lists.postgresql.org; Tue, 07 Feb 2023 08:30:55 +0000 Received: by mail-wr1-x436.google.com with SMTP id bk16so12708484wrb.11 for ; Tue, 07 Feb 2023 00:30:51 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=cybertec-at.20210112.gappssmtp.com; s=20210112; h=mime-version:user-agent:content-transfer-encoding:references :in-reply-to:date:to:from:subject:message-id:from:to:cc:subject:date :message-id:reply-to; bh=33p7EMdrFMEEunGMmb5dxSyE2sCx2+UQEvMY23mqlug=; b=KJdGhXQyA5+xCVAdaxIXOSsJEz2GIE+8GdDmue2P6EXTeWuQ8+SEfJ6HcwKTWmpYSr toeFsSZBv32dy6ht0WMBgmTXb32Nq61RZCb3CodiWrq56QCMMzDScn4fT8hqlnbRePDi XVhjuNGuxSOXjCsCEZEjSsg20B5xPZFs7PAcRVtTblI9Bhmuf2lbceeIpU6w49Bp6sEV +R1mCCzfX5JYol0xqRPtLKwRxiBJ8eV29VezhA7HZt40OKSXcVR/WJp9w+djBISzNuLk IUSca84zIsUEhEo4wmHogyS3rUsP5KIrsV4vjm/Eka8RvjWP+a99w1Nhm9l8ogQKk+Y+ LipA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=mime-version:user-agent:content-transfer-encoding:references :in-reply-to:date:to:from:subject:message-id:x-gm-message-state:from :to:cc:subject:date:message-id:reply-to; bh=33p7EMdrFMEEunGMmb5dxSyE2sCx2+UQEvMY23mqlug=; b=mDxcMi/DBYNCl4ApBHHINMhfiuvolwaCMww9p1rIgQ/pWkWXh9MVOL9NwCqsM7S8tI fSuoXQHbqip1fu/uoQ6R/ebZUGxTwVgXgJpW3m0za/ByTOACJg3ztV9XSEw3PZz8m4vr nK2nCyZZ6SJSBmq77OFo9EkQpy5FEYyBO938IKyQ6jDi2S2mqKOny3IskXCnkDhpUMCJ wYMSYNMrktVpok/PoZWc3Am02I9g4TB0ze1O3jpTNtTlUl8IBrPHh/9Uo2leOqSFZkvm Ik81gm45xBsEyLTgbf2TA/IHiWA7l9gPOTzlUWN0MlNNFZojSJOXnad2XPNAZV6XRZ7N 57vA== X-Gm-Message-State: AO0yUKUyfUYuQ0lf+9mX18KfNCMZUu/aoIWAdqnAolOXG5bSYl5Upeht Yibmpyg4h4gxZnK8fy3y0vsGjw== X-Google-Smtp-Source: AK7set/zqniyVQo3B+d2c1F4jLiUn0VWa23hIBVuxQstCKwuKG9tBQr/0alPtHsayagPgPBTz3EsQQ== X-Received: by 2002:a5d:590c:0:b0:2c3:ea78:35be with SMTP id v12-20020a5d590c000000b002c3ea7835bemr1648697wrd.19.1675758649321; Tue, 07 Feb 2023 00:30:49 -0800 (PST) Received: from localhost.localdomain ([88.116.133.170]) by smtp.gmail.com with ESMTPSA id a17-20020a5d53d1000000b002c3ebbc9248sm3610544wrw.66.2023.02.07.00.30.48 (version=TLS1_3 cipher=TLS_AES_256_GCM_SHA384 bits=256/256); Tue, 07 Feb 2023 00:30:48 -0800 (PST) Message-ID: <6442cf200b9ba3d86e695172c4da98a802bda48e.camel@cybertec.at> Subject: Re: ALTER COLUMN to change GENERATED ALWAYS AS expression? From: Laurenz Albe To: Philip Semanchuk , PostgreSQL General Date: Tue, 07 Feb 2023 09:30:48 +0100 In-Reply-To: <4C775CB6-D73C-42B6-A94E-40C495AAA521@americanefficient.com> References: <4C775CB6-D73C-42B6-A94E-40C495AAA521@americanefficient.com> Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable User-Agent: Evolution 3.46.3 (3.46.3-1.fc37) MIME-Version: 1.0 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On Mon, 2023-02-06 at 12:04 -0500, Philip Semanchuk wrote: > I have a column defined GENERATED ALWAYS AS {my_expression} STORED. I=E2= =80=99d like to change the > {my_expression} part. After reading the documentation for ALTER TABLE > (https://www.postgresql.org/docs/current/sql-altertable.html) and trying = a few things that > resulted in syntax errors, there doesn=E2=80=99t seem to be a way to alte= r the column=E2=80=99s GENERATED > expression in place. It seems like my only option is to drop and re-add t= he column. > Is that correct? I think that is correct. But changing the expression would mean rewriting = the column anyway. The only downside is that a dropped column remains in the table, a= nd no even a VACUUM (FULL) will get rid of it. Yours, Laurenz Albe