PostgreSQL, Row Count, Transactions
Posted: Sat Oct 11, 2008 12:56 pm
I see that one key difference between MySQL and PostgreSQL is that there is no LIMIT clause on PostgreSQL with UPDATE or DELETE, but there is with MySQL. So, if you run:
DELETE FROM transactions WHERE id = 40;
That could be one or many rows. Sometimes that might be important because if you ever had a mistake in your WHERE clause, you don't end up deleting the entire table or more rows than you intended.
So, on MySQL, it was cool to do:
DELETE FROM transactions WHERE id = 40 LIMIT 1;
And likewise with UPDATE.
But on PostgreSQL, I can't do this. Instead, it appears I need to build a transaction, run the statement, check how many rows were affected, and then if it was more than 1 row, to roll the transaction back. Does anyone have a piece of code that does this?
DELETE FROM transactions WHERE id = 40;
That could be one or many rows. Sometimes that might be important because if you ever had a mistake in your WHERE clause, you don't end up deleting the entire table or more rows than you intended.
So, on MySQL, it was cool to do:
DELETE FROM transactions WHERE id = 40 LIMIT 1;
And likewise with UPDATE.
But on PostgreSQL, I can't do this. Instead, it appears I need to build a transaction, run the statement, check how many rows were affected, and then if it was more than 1 row, to roll the transaction back. Does anyone have a piece of code that does this?