Thread: Feature Proposal: Column-Level DELETE Operation in SQL

Am Mon, Apr 21, 2025 at 10:23:30PM +0530 schrieb Abhishek Hatgine:

>    *deleting* a value is conceptually different from *updating* it to NULL.

In what way ?

In other words, you'll have to much better define what you
mean be "deleting a value".

DROP COLUMN already exists, for that matter.

Updating a column to its DEFAULT value may also be closer to
what you envision a "column-delete" to do.

Karsten
--
GPG  40BE 5B0E C98E 1713 AFA6  5BC0 3BEA AC80 7D4F C89B

On Tue, Apr 22, 2025 at 5:56 PM Karsten Hilbert <Karsten.Hilbert@gmx.net> wrote:
>
> Am Mon, Apr 21, 2025 at 10:23:30PM +0530 schrieb Abhishek Hatgine:
>
> >    *deleting* a value is conceptually different from *updating* it to NULL.
>
> In what way ?
>
> In other words, you'll have to much better define what you
> mean be "deleting a value".
>
> DROP COLUMN already exists, for that matter.
>
> Updating a column to its DEFAULT value may also be closer to
> what you envision a "column-delete" to do.
>

Agreed, this is a much better approach to achieve the same.

I am a bit skeptical about whether the proposed syntax will be
acceptable if it's not SQL standard, unless it's absolutely necessary
to support functionality that can't be achieved with the existing
syntax which is not the case here.

Regards,
Amul

On Mon, 2025-04-21 at 22:23 +0530, Abhishek Hatgine wrote:
> I'd like to propose a new feature for consideration in future versions of SQL — the ability
> to perform a column-level DELETE operation, allowing removal of specific column values
> without affecting the entire row.

In PostgreSQL, it affects the entire row anyway...

> Proposal Summary
> Currently, SQL provides two core commands:
>  *
>    DELETE – to remove entire rows.
>  *
>    UPDATE – to change or nullify column values.
>
> However, there’s no specific, expressive way to delete the value of a column directly.
> The typical workaround is to use:
>
> UPDATE Customers SET Address = NULL WHERE CustomerID = 103;
>
> While this works fine, it doesn't semantically express that the developer intends to
> remove the value — not just update it.
>
> Proposed Syntax Examples
> Here are some ideas for possible new syntax:
>
> DELETE Address FROM Customers WHERE CustomerID = 103;
> -- or
> REMOVE COLUMN Address FROM Customers WHERE CustomerID = 103;
> And even:
> DELETE Address, PostalCode FROM Customers WHERE Country = 'India';
>
> These would act as a shortcut or expressive alias for setting one or more column values
> to NULL.
>
> Why This Matters
>  * Improved readability and code clarity.
>  * More intuitive for developers coming from languages or NoSQL systems where fields can be
>    "deleted" from an object/document.
>  * Emphasizes intent: deleting a value is conceptually different from updating it to NULL.
>  * Opens doors for potential enhancements in tooling and IDE support.
> I understand this would require careful consideration within the SQL standards, but I
> believe it could make SQL more expressive and beginner-friendly while preserving its power.

My immediate gut reaction is "no, thank you".
For anybody who knows SQL, the code would become *less* readable.

But I'd say that PostgreSQL is the wrong place to propose this change.  We are unlikely
to implement SQL syntax that deviates from the standard like that.
You should try to convince the SQL standard committee to accept that new syntax, then
we'd feel more motivated to implement it.

Apart from my strong gut reaction, I have some techical problems with your proposed
syntax:

- If you REMOVE or DELETE a column in a row, will it always become NULL or should it
  become the DEFAULT value?  With an UPDATE, that is clear: either you say
  "SET col = NULL" or "SET col = DEFAULT".  So the UPDATE syntax is actually clearer.

- Also, the proposed syntax could easily be confused with "ALTER tab DROP col", which
  actually removes the column from a table.  SQL users would be confused by your syntax,
  because they would expect that if you REVOVE or DELETE a column, it would no longer
  be there.

Yours,
Laurenz Albe

On Monday, April 21, 2025, Abhishek Hatgine <hatgineabhishek99@gmail.com> wrote:.

• More intuitive for developers coming from languages or NoSQL systems where fields can be "deleted" from an object/document.

On Tue, 22 Apr 2025, 13:09 Abhishek Hatgine,
<hatgineabhishek99_at_gmail.com_thombrown1979@duck.com> wrote:
Dear SQL Development Team,
>
> I hope this message finds you well.
>
> I'd like to propose a new feature for consideration in future versions of SQL — the ability to perform a column-level
DELETEoperation, allowing removal of specific column values without affecting the entire row. 
>
> Proposal Summary
>
> Currently, SQL provides two core commands:
>
> DELETE – to remove entire rows.
>
> UPDATE – to change or nullify column values.
>
> However, there’s no specific, expressive way to delete the value of a column directly. The typical workaround is to
use:
>
> UPDATE Customers SET Address = NULL WHERE CustomerID = 103;
>
> While this works fine, it doesn't semantically express that the developer intends to remove the value — not just
updateit. 
>
> Proposed Syntax Examples
>
> Here are some ideas for possible new syntax:
>
>
> DELETE Address FROM Customers WHERE CustomerID = 103;
> -- or
> REMOVE COLUMN Address FROM Customers WHERE CustomerID = 103;
>
> And even:
>
> DELETE Address, PostalCode FROM Customers WHERE Country = 'India';
>
> These would act as a shortcut or expressive alias for setting one or more column values to NULL.
>
> Why This Matters
>
> Improved readability and code clarity.
>
> More intuitive for developers coming from languages or NoSQL systems where fields can be "deleted" from an
object/document.
>
> Emphasizes intent: deleting a value is conceptually different from updating it to NULL.
>
> Opens doors for potential enhancements in tooling and IDE support.
>
> I understand this would require careful consideration within the SQL standards, but I believe it could make SQL more
expressiveand beginner-friendly while preserving its power. 
>
> Thank you for your time and for all the work you do to maintain and improve SQL systems

Hi,

I don't agree that this is intuitive. When you DELETE a row, the row
is gone. The equivalent for columns would be to DROP a column, which
is not the functionality you are asking for. Setting something to NULL
is a perfectly fine operation if you need to unset the value of that
column.

I think what you are proposing would just be syntactic sugar, but is
likely to cause confusion about what exactly it does, and no less
verbose than an explicit UPDATE.

Bear in mind, you also have to contend with default values, NOT NULL
constraints, and foreign key constraints, which further complicate
matters.

Regards

Thom

Notice of Confidentiality: **This E-mail and any of its attachments may contain Lincoln National Corporation proprietary information, which is privileged, confidential, or subject to copyright belonging to the Lincoln National Corporation family of companies. This E-mail is intended solely for the use of the individual or entity to which it is addressed. If you are not the intended recipient of this E-mail, you are hereby notified that any dissemination, distribution, copying, or action taken in relation to the contents of and attachments to this E-mail is strictly prohibited and may be unlawful. If you have received this E-mail in error, please notify the sender immediately and permanently delete the original and any copy of this E-mail and any printout. This email and its attachments may collect your personal information to improve Lincoln’s products or to provide you with services related to its products. For more information, please see our privacy policy. Thank You.**

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 — the ability to perform a column-level
DELETEoperation, allowing 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 = 1, b=2, c=3 ), and ( a=11,
b=22, c=33) how do you want it to look like when "removing the
specific column value in a=1?

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

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

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

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 = 103;
> REMOVE COLUMN Address FROM Customers WHERE CustomerID = 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=1.

> 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 where 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 updating 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 standards, but I believe it could make SQL more
expressiveand beginner-friendly 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.

Francisco Olarte <folarte@peoplecall.com> writes:
> On Tue, 22 Apr 2025 at 14:09, Abhishek Hatgine
> <hatgineabhishek99@gmail.com> wrote:
>> However, there’s no specific, expressive way to delete the value of a column directly. The typical workaround is to
use:
>> UPDATE Customers SET Address = NULL WHERE CustomerID = 103;
>> While this works fine, it doesn't semantically express that the developer intends to remove the value — not just
updateit. 

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

Yeah, that.  The entire foundation of SQL, and relational databases in
general, is that a table is an array with rows and columns, and there
is something in every cell of that array --- maybe only a NULL value,
but something.  You can't expect to change that without pretty much
earth-shattering consequences.  As an example, are you suggesting
that it should be an error to try to fetch that specific column, but
only from that row?  There's not a SQL application on the planet that
wouldn't be broken by such behavior.

I grant that the notion of "no, it's not there" makes sense in some
NoSQL applications, and that's fine.  But there are reasons why NoSQL
is not SQL, and this is one.

You can already approximate this sort of behavior in Postgres by
storing the less-structured aspects of your data in a JSON or XML
column.  I'd suggest pursuing that approach rather than trying
to get us to mangle fundamental SQL semantics beyond recognition.

            regards, tom lane

> On Apr 21, 2025, at 09:53, Abhishek Hatgine <hatgineabhishek99@gmail.com> wrote:
> However, there’s no specific, expressive way to delete the value of a column directly. The typical workaround is to
use:
> UPDATE Customers SET Address = NULL WHERE CustomerID = 103;

I'm not sure I agree that's unexpressive.  When you consider the relational model, it's not clear to me what "deleting
thevalue of a column" means.  It could be: 

1. Removing the current value, which begs the question of "replacing it with what?"
2. Dropping the column, for which we already have ALTER TABLE ... DROP COLUMN.

Can you give a bit more detail about what the state of the table is when you have "deleted the value of a column
directly"?

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
--