MIME-Version: 1.0
References: <CAD2voTQMUmNCKBok-_HvYLqFNECRWNkvqPruq0NcsFgd6g-Odw@mail.gmail.com>
In-Reply-To: <CAD2voTQMUmNCKBok-_HvYLqFNECRWNkvqPruq0NcsFgd6g-Odw@mail.gmail.com>
From: Francisco Olarte <folarte@peoplecall.com>
Date: Wed, 23 Apr 2025 17:37:53 +0200
Message-ID: <CA+bJJbxTXhUD2MjQgzJnAL-md1zRjmD7USvNXB90=W9_41NJMA@mail.gmail.com>
Subject: Re: Feature Proposal: Column-Level DELETE Operation in SQL
To: Abhishek Hatgine <hatgineabhishek99@gmail.com>
Cc: pgsql-general@postgresql.org
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: <https://www.postgresql.org/message-id/CA%2BbJJbxTXhUD2MjQgzJnAL-md1zRjmD7USvNXB90%3DW9_41NJMA%40mail.gmail.com>
Precedence: bulk

On Tue, 22 Apr 2025 at 14:09, Abhishek Hatgine
<hatgineabhishek99@gmail.com> wrote:
...
> I'd like to propose a new feature for consideration in future versions of=
 SQL =E2=80=94 the ability to perform a column-level DELETE operation, allo=
wing removal of specific column values without affecting the entire row.

You will need to explain what you mean by that removal.
I.e, if I have a table with two rows ( a =3D 1, b=3D2, c=3D3 ), and ( a=3D1=
1,
b=3D22, c=3D33) how do you want it to look like when "removing the
specific column value in a=3D1?

...
> Currently, SQL provides two core commands:
> DELETE =E2=80=93 to remove entire rows.
> UPDATE =E2=80=93 to change or nullify column values.
Because these are the operations allowed in the relational model.

> However, there=E2=80=99s no specific, expressive way to delete the value =
of a column directly. The typical workaround is to use:
> UPDATE Customers SET Address =3D NULL WHERE CustomerID =3D 103;
> While this works fine, it doesn't semantically express that the developer=
 intends to remove the value =E2=80=94 not just update it.

If you want to remove the value, you need to specify how it does look
like, i.e. in my example:
( a =3D 1,  c=3D3 ), and ( a=3D11, b=3D22, c=3D33)
This is not allowed in relational, rows need to have the same structure.
( a =3D 1,  b=3D*deleted* c=3D3 ), and ( a=3D11, b=3D22, c=3D33)

This is what update does, using NULL for *deleted* ( NULL normaly
means more "unknown".

In your example, if you distinguish between deleted and unknown, you
can use an array, limited to 0 or 1 elements, and use { null } for
unknown, {} empty array for deleted.

But there is not a concept of removed value in relational.

> Proposed Syntax Examples
> Here are some ideas for possible new syntax:
> DELETE Address FROM Customers WHERE CustomerID =3D 103;
> REMOVE COLUMN Address FROM Customers WHERE CustomerID =3D 103;

And how you do propose them to work, i.e. explain to me how my simple
example would look after
delete b from example where a=3D1.

> These would act as a shortcut or expressive alias for setting one or more=
 column values to NULL.

Just set to null? You only have thought of trivial examples and
syntactic sugar. Adding new commands comes with a heavy weight for
every one, they have to be maintained, they need to be learnt in case
some other team member uses this. It sounds like a terrible idea.

> Why This Matters
> Improved readability and code clarity.

New keywords do not improve readability.

> More intuitive for developers coming from languages or NoSQL systems wher=
e fields can be "deleted" from an object/document.

No. They mislead them to think rows do not have a fixed schema, where
they have it.

> Emphasizes intent: deleting a value is conceptually different from updati=
ng it to NULL.

Not in your example, you have proposed to do the same thing. The
problem is sql is relational, relational is fixed schema, so you
cannot "remove a column value" like you can in some NoSQL ( which are
normally json++ stores ).

> I understand this would require careful consideration within the SQL stan=
dards, but I believe it could make SQL more expressive and beginner-friendl=
y while preserving its power.

Then try to write it a standard proposal. It is not that trivial.

SQL is a base tool, if you want more expresiveness in your code you
should probably just use any of the mapper technologies there are
around. It is not that hard to make an SQL++ filter which translates
this kinds of things to SQL.

Francisco Olarte.