Agreed, I've long been thinking that DELETE should require a WHERE clause.

If you really want to just delete everything, just do WHERE 1=1.

UPDATEs should require a WHERE clause too.

At which point we could just say all SQL should have a WHERE clause.

I think SELECTs without WHERE clauses are fine

But requiring them is fine too. Even more on Postrges, that has booleans.

And queries should start with WHERE.

FROM, then WHERE, then SELECT

Pipe Syntax In SQL

https://research.google/pubs/sql-has-problems-we-can-fix-the...

https://news.ycombinator.com/item?id=41338877

This is the way.

It would be nice if there was a psql option that would warn you if you're about to do any kind of update, delete or insert without doing a BEGIN;

(You could always set autocommit=false, which forces you to explicitly commit or rollback every action, but that has its own baggage)

MySQL has this as default as far as I recall. But then I never delete, I just set "deleted" to yes.

That just wouldn’t fly where you have business customers, many insist on data deletion at the end of contracts. In practice though partitioning or using seperate databases can be a better strategy for dealing with that as otherwise dealing with backups are challenging.

It might depend on the version, but last time I checked, DELETEing an entire table was much slower than TRUNCATE TABLE.

I'm pretty sure that only applies to Postgres.

Sybase SQLAnywhere as well, and not unlikely MSSQL too given its shared ancestry.

Delete with WHERE is sufficiently slow in MSSQL we have to do batched deletes, but I can't recall offhand if that holds for whole table deletion as well.

You're probably thinking of the --safe-updates option [1] for the `mysql` CLI, also available as the memorable alias --i-am-a-dummy. This requires UPDATE and DELETE to have either a WHERE clause or a LIMIT clause. Under the hood, the command-line client option just manipulates the sql_safe_updates session variable [2] to enforce the UPDATE and DELETE requirement, as well as a couple other unrelated variables to prevent overly-huge SELECTs.

It's not enabled by default out of the box, but some companies do override their configuration to enable it for new sessions, iirc Facebook did this.

[1] https://dev.mysql.com/doc/refman/8.4/en/mysql-tips.html#safe...

[2] https://dev.mysql.com/doc/refman/8.4/en/server-system-variab...

There is pg-safeupdate: https://github.com/eradman/pg-safeupdate

That is why you always get in the habit of wrapping your stuff in “BEGIN TRANSACTION”. Then if and when you fuck up you can issue a rollback and be all good.

I’ve deleted the internet??