PostgreSQL - Delete values with conditions

Question

In a PostgreSQL table, I wan't to delete some values with conditions. This conditions are based on the begining of a field with a substr. In my example, I want to delete the values not starting with '01' or '02' or '03'. When I first run a SELECT on my values with conditions, it's ok.

Sample data

id  |  serial_number
---------------------
1   | 01A
2   | 01B
3   | 02A
4   | 02B
5   | 03A
6   | 03B
7   | 03C
8   | 04A
9   | 05A
10  | 06A

Example of a selection

SELECT * FROM my_table
WHERE substr(serial_number, 1, 2) LIKE '01' OR substr(serial_number, 1, 2) LIKE '02' OR substr(serial_number, 1, 2) LIKE '03'

But when I apply the same conditions in a DELETE and add a negation, it removes eveything. I need to change operators from OR to AND.

Delete with same condition (not expected result)

DELETE FROM my_table
WHERE substr(serial_number, 1, 2) NOT LIKE '01' OR substr(serial_number, 1, 2) NOT LIKE '02' AND substr(serial_number, 1, 2) NOT LIKE '03'

Delete with different operators (expected result)

DELETE FROM my_table
WHERE substr(serial_number, 1, 2) NOT LIKE '01' AND substr(serial_number, 1, 2) NOT LIKE '02' AND substr(serial_number, 1, 2) NOT LIKE '03'

How multiple conditions could be true with a AND ? It's not cumulative. I don't understand why I need to change operators.

JNevill · Accepted Answer · 2021-08-31 15:47:17Z

3

Some basic boolean math:

True OR False = True
True AND False = False

Consider a single record in your table with value 01A. You don't want to delete this since it's in your list 01, 02, 03.

Running your logic:

substr(serial_number, 1, 2) NOT LIKE '01' = False
substr(serial_number, 1, 2) NOT LIKE '02' = True

Taking that back to the Boolean logic above you'll see that False OR True = True which means we delete the record. However, switching to an AND means False AND True = False and your record 01A is retained as expected.

This is a common issue when dealing with negation and booleans in that it doesn't match how we use OR in english.

Consider a store that cells gift cards in values 20, 50 and 100 dollars:

"Can you go get a gift card for Chili's restaurant. I can't remember what dollar amounts they sell, but I don't need the 20 dollar one or the 50 dollar one"

We all understand perfectly well that they don't want the 20 or the 50, but rather the 100 dollar gift card. But in boolean logic the person being asked would return with ALL the gift cards available as 20 != 50 and 50 != 20 and 100 != 50 (and 20). So the OR sees a TRUE for at least one of the conditions specified regardless of the gift card amount.

answered Aug 31, 2021 at 15:47

JNevill

50.6k4 gold badges46 silver badges72 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

GeoGyro Over a year ago

As you said this usage of negation and booleans doesn't match with natural language, it confused me. Thank you for this well-illustrated example and the detailed explanations.

bfris Over a year ago

Nice explanation. I must be turning into a crusty engineer (back in my day...) because boolean logic is so ingrained that it doesn't occur to me that boolean logic differs from natural language. Does this mean I have to cut more slack to young engineers?

JNevill Over a year ago

@bfris Every time I think I have it down I encounter some negation thing that blows my mind. I had a hell of a time getting my head wrapped around NOT LIKE ALL (<list>) vs NOT LIKE ANY (<list>) in Teradata. I still have to really sit down and think about it before I use those.

Collectives™ on Stack Overflow

PostgreSQL - Delete values with conditions

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related