Date: Wed, 23 Apr 2025 14:05:31 -0500
From: Nico Williams <nico@cryptonector.com>
To: Abhishek Hatgine <hatgineabhishek99@gmail.com>
Cc: pgsql-general@postgresql.org
Subject: Re: Feature Proposal: Column-Level DELETE Operation in SQL
Message-ID: <aAk5+zyZ9ztVOi7F@ubby>
References: <CAD2voTQMUmNCKBok-_HvYLqFNECRWNkvqPruq0NcsFgd6g-Odw@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <CAD2voTQMUmNCKBok-_HvYLqFNECRWNkvqPruq0NcsFgd6g-Odw@mail.gmail.com>
Archived-At: <https://www.postgresql.org/message-id/aAk5%2BzyZ9ztVOi7F%40ubby>
Precedence: bulk

On Mon, Apr 21, 2025 at 10:23:30PM +0530, Abhishek Hatgine wrote:
> These would act as a shortcut or expressive alias for setting one or more
> column values to NULL.

NULL values are not quite no-values, and setting some column of some row
to NULL is not quite the same as deleting the column from the row.

Even if the database's row encoding is such that a "null value" is not
written there will be bits that indicate that the column's value is
null.

Now in an entity-attribute-value (EAV) sort of schema setting a column
to NULL could be made to be the same as deleting that one row.  But PG
does not currently map tables to EAV stores, though I suppose it could
eventually have table types that do just that, but that doesn't mean PG
would need a new statement type.

Even an RDBMS that only used an EAV metaschema internally would not need
a new statement type.  Setting a column value to NULL is just not
"deleting a colun value".

Having two ways to do this doesn't seem necessary at all.

Moreover, with UPDATE one can set some columns to NULL values and others
to non-NULL values in the same statement, whereas one could not do that
with the new statement type you propose.

[At this point this is just piling on, for which my apologies, but I
thought the EAV thing might be worth noting.]

Nico
--