Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1u9SYr-00247c-KH for pgsql-admin@arkaria.postgresql.org; Mon, 28 Apr 2025 17:46:34 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1u9SYp-002Ufx-AN for pgsql-admin@arkaria.postgresql.org; Mon, 28 Apr 2025 17:46:32 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1u9SYo-002Ufp-Vk for pgsql-admin@lists.postgresql.org; Mon, 28 Apr 2025 17:46:32 +0000 Received: from mail-oo1-xc2a.google.com ([2607:f8b0:4864:20::c2a]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1u9SYn-0002r6-2k for pgsql-admin@lists.postgresql.org; Mon, 28 Apr 2025 17:46:31 +0000 Received: by mail-oo1-xc2a.google.com with SMTP id 006d021491bc7-6021e3daeabso2702583eaf.3 for ; Mon, 28 Apr 2025 10:46:29 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1745862387; x=1746467187; darn=lists.postgresql.org; h=to:subject:message-id:date:from:in-reply-to:references:mime-version :from:to:cc:subject:date:message-id:reply-to; bh=85whJRwVMmDvjZwxLHUhOqZPkaZtogA1ep7iPPYvQXQ=; b=KbOfgz4gj8TxehsuhB5Oiuamrhy/nRw51JJidcWg225u59lC/FL3MDL5H7p8ivpXTD rafK5S3TfkaaktxYymnQUvVPzDTrzroKY9KcXpSbUyTZHEoICB6Kn4o8XNkQAlfKelOd WTLKc9snbZxhEL4hEBuqk8eKzx8twzeMNSHxivDCeECXJLIBN2+OXQJu8lkedJfagIZI s+LXYoExaA0jKhWmmCEu0+9uwtg3PEMxTnCHMxDu8uyPWkHo/xfLCwrZn9Lj384l5Ip7 TfoG4uY+QeLMnh7HnNsaIdaT4KTPJBvkKLJA/4ez+9x2MjfgIibK4uOTUR1IBjZfBcvY st+A== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1745862387; x=1746467187; h=to:subject:message-id:date:from:in-reply-to:references:mime-version :x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=85whJRwVMmDvjZwxLHUhOqZPkaZtogA1ep7iPPYvQXQ=; b=V4jZWIsJNBFFy9fqOhC+S/rux91Bu1wsVhJu1/xJLSqJ6ERsI9E9rvwd14bhz4qDaJ vo1yEtykjdadyQ3surnHZqOrFKQRLSlu3s1zDKVO2ixuk6JqLQejSvZW8leIbIWqNLAr K4zsgECWYMldjsnnlGET7RbYB9vu79VEJJyu3R67X+xvI0ZVLPYeUVTXtP+pihBNR1eI smfOvBuETnen5acQW1g7WGr1VRp/WNwZchIZNgDhl0YCGl2CPL/e3LyLJZxnaJCSOjIr uMIN9R+7W3SlKmVER35bTR4pD/PTF3pqZ+frQBhTAE3ZknIqP9ulDQHO33Ier3ItVbHd 8ONw== X-Gm-Message-State: AOJu0YxwUe5t9NtwC+m7hCfIeMRZsHCTOkPoU+vfh20S1QsxYTYjAEjj tMiyHkhLxgDZc+18tBIC9voeOBg79F2o6Oy9YNIVgl572qfdGtiHfKeM30/vcXl+Zm56sFk366I c/sXJYSz7SI08C4VHEWoHg28fbMPPrA== X-Gm-Gg: ASbGncuq7u7IkH1xaEl0cREv52Xbdjj7E2YyKxNhc6E4SSdlqN73nBBBB1Df9MxmlzI iyqi/UsW77/kSXYIJNki9W+Mn3PKFYKIeI/kLUO3f+8qTCbzSqrfcwuxUwe+qRVE5JRbHNZUmFs uWPdyHNaXTcTaHUFIT1Men/kQ= X-Google-Smtp-Source: AGHT+IFfd5/5D5OqS/ckiI+lh1u6r72Of9SqoSrGFMr2XiIAkyvY1C/uSBuajtm6zpUlYMKoWVVUXmtts7QmEZIeVFQ= X-Received: by 2002:a05:6820:298f:b0:602:7078:df55 with SMTP id 006d021491bc7-60652bc28bfmr7008718eaf.5.1745862386840; Mon, 28 Apr 2025 10:46:26 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Ron Johnson Date: Mon, 28 Apr 2025 13:46:15 -0400 X-Gm-Features: ATxdqUHBncbTg6bRXKrgJnpvnYhi12OrQKoQS2UyrIa2dI7NN1VGVuyo-HQEeDk Message-ID: Subject: Re: Adding New Column with default value. To: Pgsql-admin Content-Type: multipart/alternative; boundary="00000000000064f7ac0633da43f3" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --00000000000064f7ac0633da43f3 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Mon, Apr 28, 2025 at 1:25=E2=80=AFPM Gambhir Singh wrote: > > I got request from app Team to add new column to a table with following > specifications > > 1. With Not Null Constraint > 2. With Default value 0 > > Row Count - 50 Billion > > Please help me to understand > a. DDL statement (alter table add column) will be followed by the DML > statement (Update)....Am I correct ? > Do you want existing rows to have a value of 0, or just new ones? > b. What would be size of the Table (I assuming it will be doubled) > It will double if the table currently only has one scalar column. > c. What is the best approach for this kind of activity. > The ALTER TABLE documentation is pretty long, but searching it for "update", and reading carefully gives pointers. --=20 Death to , and butter sauce. Don't boil me, I'm still alive. lobster! --00000000000064f7ac0633da43f3 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On Mon, Apr 28, 2025 at 1:25=E2=80=AFPM G= ambhir Singh <gambhir.singh= 05@gmail.com> wrote:
<= div>
I got=C2=A0 request from app Team to=C2= =A0 add new column to a table with following specifications

1. With Not Null Constraint
2. With Default value 0
<= br>
Row Count - 50 Billion

Please help me to u= nderstand
=C2=A0 a. DDL statement (alter table add column) wi= ll be followed by the DML statement (Update)....Am I correct ?
<= /blockquote>

Do you want existing rows to have a value o= f 0, or just new ones?
=C2=A0
=C2=A0 b. What would be size of the= Table (I assuming it will be doubled)

It will double if the table currently=C2=A0only has one scalar colu= mn.
=C2=A0
=C2=A0 c. What is the best approach fo= r this kind of activity.

The AL= TER TABLE documentation is pretty long, but searching it for "update&q= uot;, and reading carefully gives pointers.

--
Death to <Redacted>, and butter sauc= e.
Don't boil me, I'm still alive.
<Redacted>= ; lobster!
--00000000000064f7ac0633da43f3--