"UPDATE table_name SET w = $1, x = $2, z = $4 WHERE y = $3 RETURNING *",
does not do the same as
"UPDATE table_name SET w = $1, x = $2, y = $3, z = $4 RETURNING *",
It’s 2 am and my mind blanked out the WHERE, and just wanted the numbers neatly in order of 1234.
idiot.
FML.
SQL scouts credo: I will never use indexes, I will always use column names.
You all run queries against production from your local? Insanity.
Everyone has a production system. Some may even have a separate testing environment!
This is a hard lesson to learn. From now on, my guess is you will have dozens of backups.
And always use a transaction so you’re required to commit to make it permanent. See an unexpected result? Rollback.
Transactions aren’t backups. You can just as easily commit before fully realizing it. Backups, backups, backups.
Yes, but
- Begin transaction
- Update table set x=‘oopsie’
- Sees 42096 rows affected
- Rollback
Can prevent a restore, whereas doing the update with auto commit guarantees a restore on (mostly) every error you make
this folks, is why you don’t raw dog sql like some caveman
Raw dog is the fastest way to finish a task.
- productivity
- risk
It’s a trade-off
There’s no way you’re endorsing the way OP handled their data right?
No, but people are sometimes forced to do these things because of pressure from management and/or lack of infrastructure to do it in any other way.
Definitely don’t endorse it but I have done it. Think of a “Everything is down” situation that can be fixed in 1 minute with SQL.
Got it. I’m with you.
I don’t know if it makes you feel better but Tom Scott had a similar experience: https://youtu.be/X6NJkWbM1xk
