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 1pPTCo-0003c1-QM for pgsql-general@arkaria.postgresql.org; Tue, 07 Feb 2023 19:00:38 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pPTCn-0003f9-Mo for pgsql-general@arkaria.postgresql.org; Tue, 07 Feb 2023 19:00:37 +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 1pPTCn-0003f0-7y for pgsql-general@lists.postgresql.org; Tue, 07 Feb 2023 19:00:37 +0000 Received: from mail-ej1-x62f.google.com ([2a00:1450:4864:20::62f]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1pPTCh-0004VW-34 for pgsql-general@lists.postgresql.org; Tue, 07 Feb 2023 19:00:36 +0000 Received: by mail-ej1-x62f.google.com with SMTP id lu11so45187713ejb.3 for ; Tue, 07 Feb 2023 11:00:30 -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=c+pNDECv93idRsBLwsOTMS11DOHZ5rUXSI9a/XQ+rR4=; b=IMPEmN9sIuOVHwBlP4+nCJfn1YpstMNncukwOYjUmaBjckfzeWotk/GqZ0OGN/FOAc gP6vQv6mE6hvOxmHpRdsV2Fd0LUDq96Pgs8ohVn29yeXohdI0Bvl4e4kCbzISjO3L0GY uIJbE3CLE/JK97cj9PO9jM6u7ARHyjAbl7jo5117SiUqK98mHgqAccdGcuGAZ/M1ae+W 1whIPBb0NAfDReFVz2Tr4NWzNoQp/fZ/qGyuxK7o8wZcvNRelbKB3X2KvHEq5w4afIXA tUXOtatVP143mL+FisssPE7nzald3Xx61mWFKR8aNO2SK+0KFU+jK1+SaH/V+sNrzUYY Hb2A== 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=c+pNDECv93idRsBLwsOTMS11DOHZ5rUXSI9a/XQ+rR4=; b=bZi8g3nRJKQvh7xdldr6nKjeVzJmwDRyaoRzw5y+oqq8uUPuncCDIFfpG5JoBEJ0hz K3Hs0QM64ZwbMop8/r//GCIRYEbVGkMk5uf2KxQSsTKn7Oa5VmQUkaaVoemZTZx42rYC jwwlrhXONP46KCePEm7g2ZmUqQcoJ1coqmjbP3GtOQNobMrFly1kpwIrowA7r2WXuK7/ IKNhCejEyBDUhmplJF61tj/dfHAeGmvXfnqUyWkfJptIRbvYmACGlT37ibdWYkzKa3lv VY9e+fvzkn/zfBE6+F796+kMXqAtmPxp3DujZPJvbirG755getRooMr+SapxWRXa6By7 vjvw== X-Gm-Message-State: AO0yUKUsr1mhzMJWyDNytyVTfnrwEBNIoKHaFhhBVO2KI0cCYVN4JFlw d7h1mR744Z8BwRdCE/3hiFz0ng== X-Google-Smtp-Source: AK7set9chs8cBqfQgJrJwGJj75ozytiWpiNtZ+OVCBM3ee/KjsbevZ2FxV3qDo8NJpIn462GYBr+Lg== X-Received: by 2002:a17:906:a242:b0:878:734d:1d87 with SMTP id bi2-20020a170906a24200b00878734d1d87mr4979554ejb.47.1675796430077; Tue, 07 Feb 2023 11:00:30 -0800 (PST) Received: from localhost.localdomain (178-190-183-242.adsl.highway.telekom.at. [178.190.183.242]) by smtp.gmail.com with ESMTPSA id g18-20020a170906595200b00878775876c5sm7305462ejr.73.2023.02.07.11.00.29 (version=TLS1_3 cipher=TLS_AES_256_GCM_SHA384 bits=256/256); Tue, 07 Feb 2023 11:00:29 -0800 (PST) Message-ID: <9fe2f3c28d1b51cdec9393111ec97b8e8d3bb7c3.camel@cybertec.at> Subject: Re: ALTER COLUMN to change GENERATED ALWAYS AS expression? From: Laurenz Albe To: Ron , pgsql-general@lists.postgresql.org Date: Tue, 07 Feb 2023 20:00:29 +0100 In-Reply-To: References: <4C775CB6-D73C-42B6-A94E-40C495AAA521@americanefficient.com> <6442cf200b9ba3d86e695172c4da98a802bda48e.camel@cybertec.at> <60ebe24e-33c4-2f56-a3ea-fc85518ecc22@aklaver.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 Tue, 2023-02-07 at 12:40 -0600, Ron wrote: > > "The DROP COLUMN form does not physically remove the column, but simply= makes it > > invisible to SQL operations. Subsequent insert and update operations in= the table > > will store a null value for the column. Thus, dropping a column is quic= k but it > > will not immediately reduce the on-disk size of your table, as the spac= e occupied > > by the dropped column is not reclaimed. The space will be reclaimed ove= r time as > > existing rows are updated.=20 > >=20 > > To force immediate reclamation of space occupied by a dropped column, y= ou can > > execute one of the forms of ALTER TABLE that performs a rewrite of the = whole table. > > This results in reconstructing each row with the dropped column replace= d by a > > null value." > >=C2=A0VACUUM FULL doesn't rewrite the table? It rewrites the table, but it just copies rows rather than reconstructing t= hem. Yours, Laurenz Albe