PostgreSQL, Row Count, Transactions

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
jack_indigo
Forum Contributor
Posts: 186
Joined: Sun Jun 08, 2008 11:25 pm

PostgreSQL, Row Count, Transactions

Post by jack_indigo »

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?
Post Reply