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 1pE0bd-0005GX-U9 for pgsql-general@arkaria.postgresql.org; Sat, 07 Jan 2023 04:14:54 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pE0bc-0006KP-86 for pgsql-general@arkaria.postgresql.org; Sat, 07 Jan 2023 04:14:52 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pE0bb-0006KG-PT for pgsql-general@lists.postgresql.org; Sat, 07 Jan 2023 04:14:51 +0000 Received: from mail-oi1-x22c.google.com ([2607:f8b0:4864:20::22c]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1pE0bY-0000Dl-Kw for pgsql-general@lists.postgresql.org; Sat, 07 Jan 2023 04:14:50 +0000 Received: by mail-oi1-x22c.google.com with SMTP id r205so2676373oib.9 for ; Fri, 06 Jan 2023 20:14:48 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; h=in-reply-to:from:references:cc:to:content-language:subject :user-agent:mime-version:date:message-id:from:to:cc:subject:date :message-id:reply-to; bh=UIAiMW9lLSEYyOAAWlEMTja2nM03odI7E2eA4GT8A5A=; b=UVVMrsRcISPdzlJca1juYwGkXTDor3qtfZONysDW+5bFtSNJiNjhI4BU4ee7SIMaAv rsd9bYaOAvyL1HKu0geU6k8nyhvcZp3f9fNGv76E7wWopQ/K5Z6vee7pohvulIj7DLZB DO5LO/xW3LS/SprTqNGyBnQnvzSWV0ymd5BIagN62ZUSccKa61b/wYFkpnGUNm5IPt6p +y6heonAJxxYROnl57WelPIwDytnvcJEV5V6gEQPRRK0XM0ZdUgDN1x7gslGbwWDIfmN OsYHCN6yA3Tua950FUGbE/+6JeU6YnXcGyjM6im5lYp51MjiIiq4qxbKx6/DJhOC+p7i 4mTQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=in-reply-to:from:references:cc:to:content-language:subject :user-agent:mime-version:date:message-id:x-gm-message-state:from:to :cc:subject:date:message-id:reply-to; bh=UIAiMW9lLSEYyOAAWlEMTja2nM03odI7E2eA4GT8A5A=; b=wihdkks0b6EUOQEryHWs1YTfI91HeV/EtcjsoD/3nnqNc5ZChix6NkglydFPZtswA7 41e8JbUThKhNKHiZMSPpuA4jrJJiJ3iFk+Vbwde0DGnGIbCtqYe4gQtzN0yGu6KQ3qB5 NwkDgw6GAiQhmBYWuNv21SHeKijp+PPZxX6Mgy5SpbQ/7Dy08Qjk0V2/f4G94JpF3m8m ohsSncoyVay9bYEHOQad8YwGRJEaWQJroccOuT9hG0tziRktR90SPoUIYCVeqg6BXHDG DFwMkEfel7X3BMxwfsxyBqKUxkYFMkFm9B75mswHVEB4BXz17JsAwaqqZn+THg4vBttA LUqQ== X-Gm-Message-State: AFqh2koMN1pmmUx46NilbGTQsPN4V7QF7jW4DgTY2mllKWnTYSRjrnXQ JwtcMXt2izMtiOh2zM7kOdCTJmL1Mrk= X-Google-Smtp-Source: AMrXdXsl3HNuw4+Vo+nnC8TtHB2C2IASbKAG43ukhdjMugiPWjfFS4xaXbcMw+E1DL5UeyzMuKjNng== X-Received: by 2002:aca:1b0a:0:b0:35e:6f1d:eda9 with SMTP id b10-20020aca1b0a000000b0035e6f1deda9mr28552428oib.53.1673064887942; Fri, 06 Jan 2023 20:14:47 -0800 (PST) Received: from [192.168.1.115] (h244.184.30.69.dynamic.ip.windstream.net. [69.30.184.244]) by smtp.gmail.com with ESMTPSA id t41-20020a05680815a900b0035b451d80afsm1125068oiw.58.2023.01.06.20.14.47 (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Fri, 06 Jan 2023 20:14:47 -0800 (PST) Content-Type: multipart/alternative; boundary="------------lj09wiHmAeIUxqGU1cqzewPf" Message-ID: Date: Fri, 6 Jan 2023 22:14:47 -0600 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:102.0) Gecko/20100101 Thunderbird/102.0.2 Subject: Re: Updating column default values in code Content-Language: en-US To: Ken Tanzer Cc: pgsql-general@lists.postgresql.org References: From: Brad White In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------lj09wiHmAeIUxqGU1cqzewPf Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit On 1/6/2023 7:44 PM, Ken Tanzer wrote: > > > On Fri, Jan 6, 2023 at 3:32 PM Brad White wrote: > > I can generate ALTER statements, per David's sensible suggestion, > but they fail because you have to drop all the views. > > > Altering the defaults seems safe because the default value > shouldn't affect the view at all. > > > Are you sure those alter statements will fail?  I do that frequently.  > Plus would expect it to work because of your second statement. > > Here's an example, running on 9.6: > > CREATE TABLE foo (f1 integer DEFAULT 1, f2 integer); > CREATE VIEW foo_view AS SELECT * FROM foo; > ALTER TABLE foo ALTER COLUMN f1 SET DEFAULT 3; > ALTER TABLE foo ALTER COLUMN f2 SET DEFAULT 2; > > agency=> BEGIN; > BEGIN > agency=> CREATE TABLE foo (f1 integer DEFAULT 1, f2 integer); > CREATE TABLE > agency=> CREATE VIEW foo_view AS SELECT * FROM foo; > CREATE VIEW > agency=> ALTER TABLE foo ALTER COLUMN f1 SET DEFAULT 3; > ALTER TABLE > agency=> ALTER TABLE foo ALTER COLUMN f2 SET DEFAULT 2; > ALTER TABLE > > Cheers, > Ken > > -- > That's a good point. It was failing when I was trying to change the field type, before I stumbled on the real issue of the default values. I realize now that I assumed and didn't try to update just the defaults. Thanks, Brad. -- Quote Signature I talk with clients, find out where their pain points are, and solve those. On-call IT Management for small companies and non-profits. SCP, Inc. bwhite@inebraska.com 402-601-7990 Quote of the Day    There is a huge difference between fathering a child and being a father.    One produces a child. The other produces an adult.     -- John Eldredge --------------lj09wiHmAeIUxqGU1cqzewPf Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit
On 1/6/2023 7:44 PM, Ken Tanzer wrote:


On Fri, Jan 6, 2023 at 3:32 PM Brad White <b55white@gmail.com> wrote:
I can generate ALTER statements, per David's sensible suggestion, but they fail because you have to drop all the views. 
 
Altering the defaults seems safe because the default value shouldn't affect the view at all.

Are you sure those alter statements will fail?  I do that frequently.  Plus would expect it to work because of your second statement.

Here's an example, running on 9.6:

CREATE TABLE foo (f1 integer DEFAULT 1, f2 integer);
CREATE VIEW foo_view AS SELECT * FROM foo;
ALTER TABLE foo ALTER COLUMN f1 SET DEFAULT 3;
ALTER TABLE foo ALTER COLUMN f2 SET DEFAULT 2;

agency=> BEGIN;
BEGIN
agency=> CREATE TABLE foo (f1 integer DEFAULT 1, f2 integer);
CREATE TABLE
agency=> CREATE VIEW foo_view AS SELECT * FROM foo;
CREATE VIEW
agency=> ALTER TABLE foo ALTER COLUMN f1 SET DEFAULT 3;
ALTER TABLE
agency=> ALTER TABLE foo ALTER COLUMN f2 SET DEFAULT 2;
ALTER TABLE 

Cheers,
Ken

--

That's a good point.

It was failing when I was trying to change the field type, before I stumbled on the real issue of the default values.

I realize now that I assumed and didn't try to update just the defaults.

Thanks,
Brad.

--
Quote Signature I talk with clients, find out where their pain points are, and solve those.
On-call IT Management for small companies and non-profits.
SCP, Inc.
bwhite@inebraska.com
402-601-7990


Quote of the Day
   There is a huge difference between fathering a child and being a father.
   One produces a child. The other produces an adult.
    -- John Eldredge
--------------lj09wiHmAeIUxqGU1cqzewPf--