let ALTER TABLE DROP COLUMN drop whole-row referenced object

From: jian he <jian(dot)universality(at)gmail(dot)com>
To: PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: let ALTER TABLE DROP COLUMN drop whole-row referenced object
Date: 2025-09-08 02:53:00
Message-ID: CACJufxGA6KVQy7DbHGLVw9s9KKmpGyZt5ME6C7kEfjDpr2wZCw@mail.gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

hi.

CREATE TABLE ts (a int, c int, b int
constraint cc check((ts = ROW(1,1,1))),
constraint cc1 check((ts.a = 1)));
CREATE INDEX tsi on ts (a) where a = 1;
CREATE INDEX tsi2 on ts ((a is null));
CREATE INDEX tsi3 on ts ((ts is null));
CREATE INDEX tsi4 on ts (b) where ts is not null;

in the master, ``ALTER TABLE ts DROP COLUMN a;``
will not drop constraint cc, index tsi3, tsi4;

with the attached patch,
``ALTER TABLE ts DROP COLUMN a;``
will drop above all indexes on the table "ts" and also remove the
constraints "cc" and "cc1".

as per the documentation[1], quote:
"""
DROP COLUMN [ IF EXISTS ]
This form drops a column from a table. Indexes and table constraints involving
the column will be automatically dropped as well.
"""

so I think it's expected behavior to drop the entire
whole-row referenced indexes and constraints.

[1] https://www.postgresql.org/docs/devel/sql-altertable.html#SQL-ALTERTABLE-DESC-DROP-COLUMN

Attachment Content-Type Size
v1-0001-let-ALTER-TABLE-DROP-COLUMN-drop-wholerow-referenced-object.patch text/x-patch 9.7 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message BharatDB 2025-09-08 03:54:46 Re: Adding skip scan (including MDAM style range skip scan) to nbtree
Previous Message Chao Li 2025-09-08 02:49:44 Re: Allow logical replication in the same cluster