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 1tmbuu-0083XV-BD for pgsql-general@arkaria.postgresql.org; Mon, 24 Feb 2025 17:06:53 +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 1tmbut-005FBR-3W for pgsql-general@arkaria.postgresql.org; Mon, 24 Feb 2025 17:06:51 +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 1tmbus-005FBJ-N8 for pgsql-general@lists.postgresql.org; Mon, 24 Feb 2025 17:06:50 +0000 Received: from mail-oa1-x35.google.com ([2001:4860:4864:20::35]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1tmbuq-000ZUy-0q for pgsql-general@lists.postgresql.org; Mon, 24 Feb 2025 17:06:50 +0000 Received: by mail-oa1-x35.google.com with SMTP id 586e51a60fabf-2bc52407b78so1573065fac.0 for ; Mon, 24 Feb 2025 09:06:48 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1740416807; x=1741021607; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=WPcNtelTzCQF+XlQownYEJfjRxunBd8px1WWX0/GdsM=; b=Yg6W7dDUyDf5CXlFeOOVGGwQDOJW33VXplIuZgVSeu+5dw73HaBIPhJoJEjFpRHb50 NxAWhkTUFnKL2aGW6WPllMSNGSjcNbWJt6S7YCno+b9eZ7MgCvQ/5VGjHJNkDKz2xwXM 5V1hCRrQ7jjDpTHAjEm0pgqJ7xZBYLlaqzALDJdr06dnAabsl+raaL3sL9U/CsTkcAT9 21FiI3t9pejZrrtQnJjF/A8EBCIXNCaACSmv7NKhk01qjeeRqao1eHHnzUx5cIasmk/Y Vm9ZaH7ESww66gW9Pt72VEs3uBLUeQkuFDX9xtxMzJcTHCso9ItyK9xZlgBRgvvF+tJT coMQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1740416807; x=1741021607; h=cc: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=WPcNtelTzCQF+XlQownYEJfjRxunBd8px1WWX0/GdsM=; b=WfzYa2K80Yer4Bi4NrZDML0YG4mPoaRvuzq2O+JyaSwdSppYAbD5ROb0rSuis6QotW iCMXG8B2UsYUCLuNkvD4xLjy2NS9eExVhDm9XZ3xGS51YGroJiSMjLvnD2tfu/yJ5+A2 eUV46iwOfXLVn6T0/8pAXMKdGyopVjJCDb3BCCOq6YQdOUwnzUi9+xl9CfrZyrnuzfZK gi0nIaRboBGSNfpd63rXybWmZmPZqqhUY18acRauCqtmAgpCHX1hWN8ldqJutcsD63Me PjANFYcuWx2d9VuFWsGRyoeRDjeS/3WmCC0UXmwKW6GnVlhuOMRZ/SDZAb6zg1HHJnQH eT7g== X-Forwarded-Encrypted: i=1; AJvYcCWJ4q5Pm22aQ3GQDw6kYRgEoOJwkkIEJHg5E6I8f6pgEEQ45kUPvxJPImYYsqiqzxBVpPSX3OkieHzeiMqT@lists.postgresql.org X-Gm-Message-State: AOJu0YyZhUeA03DSkGjOKJdJg/hsCto63/bfD+Ctc39yL+M/zichTxaZ BuoZCJxNvPSMfOCl2zAJ8xCh7z5vWjXqpDnzRMqfhbuhZHjISyeGl6naxGYWPKQ+QiGoNVoTZ3M 4qohNJON57+wB9smYPe+iap4UKrNMBQ== X-Gm-Gg: ASbGncukuYU5y1fXjROKf8OuJMuwcmSrBy1HFnRvm98emPhZMCEQNQyVxgKoB0JXzcL VXXx3yU4fRFo91keHbOxCDageRCSAH62lNJOSf/6zWGhLhbr2TqmEiabJKpy6Zo+/S8V1XFUqTa u6PZ/hcA== X-Google-Smtp-Source: AGHT+IE1Kxhv7oVOAnmeo3v2fx7b/EC2widptOTCrp6+2TV3zVx29I7Dttol+D/9Gc4Hl8W7HhOhP7DTetLWt24z8dE= X-Received: by 2002:a05:6870:524a:b0:29e:3531:29da with SMTP id 586e51a60fabf-2bd50ccda00mr11794631fac.9.1740416806662; Mon, 24 Feb 2025 09:06:46 -0800 (PST) MIME-Version: 1.0 References: In-Reply-To: From: "David G. Johnston" Date: Mon, 24 Feb 2025 10:06:09 -0700 X-Gm-Features: AWEUYZlMbnGzAKpyKb9VoDVm1i61ZPPHip7cWgBDu6iXZFwysnjLg_-QoAaS6zI Message-ID: Subject: Re: Default Value Retention After Dropping Default To: Adrian Klaver Cc: Laurenz Albe , Marcelo Fernandes , pgsql-general@lists.postgresql.org Content-Type: multipart/alternative; boundary="00000000000085c5b6062ee65d93" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --00000000000085c5b6062ee65d93 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Mon, Feb 24, 2025 at 9:37=E2=80=AFAM Adrian Klaver wrote: > On 2/24/25 03:50, Laurenz Albe wrote: > > On Mon, 2025-02-24 at 20:56 +1300, Marcelo Fernandes wrote: > >> I am experiencing an interesting behavior in PostgreSQL and would like > to seek > >> some clarification. > > > >> > >> Can anyone explain how PostgreSQL "knows about" the default value that > has just > >> been dropped and what is happened under the scenes? I am keen on a dee= p > >> understanding on how Postgres achieves this. > > > > The "missing value" is stored in pg_attribute.admissingval: > > > > SELECT attmissingval > > FROM pg_attribute > > WHERE attrelid =3D 'foo'::regclass > > AND attname =3D 'bar'; > > > > attmissingval > > =E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95= =90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90 > > {default} > > (1 row) > > > > That value is used for all rows that don't yet physically have the > column. > > That answers this part of the process: > > ALTER TABLE foo ADD COLUMN bar varchar(255) NOT NULL DEFAULT 'default'; > > I believe the OP is asking about this: > > ALTER TABLE foo ALTER COLUMN bar DROP DEFAULT; > > Because if after dropping the DEFAULT you do this: > > INSERT INTO foo (id) SELECT generate_series(1001, 1010); > > You get: > > ERROR: null value in column "bar" of relation "foo" violates not-null > constraint > DETAIL: Failing row contains (1001, null). > > The DEFAULT is no longer in use, but the values still exist in the > previously entered rows: > The alter table command established a persistent substitute value for the new column, for all existing rows, when it was executed. While the value of the substitute is equal to the non-volatile default specified for the column it is an independent thing. Subsequently dropping or changing the default does not impact this substitute value. There is no way to impact the substitute value via SQL that I know of. David J. --00000000000085c5b6062ee65d93 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On Mon, Feb 24, 2025 at 9:37=E2=80=AFAM Adrian Klaver <= adrian.klaver@aklaver.com&= gt; wrote:
On 2/24/25 03:50, Laur= enz Albe wrote:
> On Mon, 2025-02-24 at 20:56 +1300, Marcelo Fernandes wrote:
>> I am experiencing an interesting behavior in PostgreSQL and would = like to seek
>> some clarification.


>>
>> Can anyone explain how PostgreSQL "knows about" the defa= ult value that has just
>> been dropped and what is happened under the scenes? I am keen on a= deep
>> understanding on how Postgres achieves this.
>
> The "missing value" is stored in pg_attribute.admissingval:<= br> >
> SELECT attmissingval
> FROM pg_attribute
> WHERE attrelid =3D 'foo'::regclass
>=C2=A0 =C2=A0 AND attname =3D 'bar';
>
>=C2=A0 =C2=A0attmissingval
> =E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95= =90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90
>=C2=A0 =C2=A0{default}
> (1 row)
>
> That value is used for all rows that don't yet physically have the= column.

That answers this part of the process:

ALTER TABLE foo ADD COLUMN bar varchar(255) NOT NULL DEFAULT 'default&#= 39;;

I believe the OP is asking about this:

ALTER TABLE foo ALTER COLUMN bar DROP DEFAULT;

Because if after dropping the DEFAULT you do this:

INSERT INTO foo (id) SELECT generate_series(1001, 1010);

You get:

ERROR:=C2=A0 null value in column "bar" of relation "foo&quo= t; violates not-null
constraint
DETAIL:=C2=A0 Failing row contains (1001, null).

The DEFAULT is no longer in use, but the values still exist in the
previously entered rows:

The alter t= able command established a persistent substitute value for the new column, = for all existing rows, when it was executed.=C2=A0 While the value of the s= ubstitute is equal to the non-volatile default specified for the column it = is an independent thing.=C2=A0 Subsequently dropping or changing the defaul= t does not impact this substitute value.=C2=A0 There is no way to impact th= e substitute value via SQL that I know of.

David J.

--00000000000085c5b6062ee65d93--