Google+ Followers

dinsdag 23 december 2014

Why I like PostgreSQL more than MySQL: REPLACE()

"UPSERT", update a record or insert it if it does not exist,  is a feature that is sorely missing from most database engines.

MySQL has implemented a fools-upsert through REPLACE(), because it's not UPSERT, it's DELETE and INSERT. The statement will first delete any existing record and then create a new one.

This has a few side effects that usually don't show up until you've spent an hour or two debugging:

1. The DELETE will trigger ON DELETE CASCADE. So if you REPLACE a user record, then all records that refer to the user with an ON DELETE CASCADE will be deleted. Then the new user record is created, and what you see is: "the user record still exists the PK is still the same, but all referencing records have disappeared. WUT?!"

2. A new record is created, which means that any fields  that are not mentioned in the REPLACE query will be left at their default values. What you see is:"I did a REPLACE to change the name and now the user's date of birth is empty. WUT?!"

3. Because the DELETE will trigger the CASCADE, you will get a foreign-key violation when you try to REPLACE a record that has relations with a RESTRICT setting. What you see is: "I try to replace a record and it says that it cannot DELETE... WUT?!"

How does PostgreSQL handle this? It doesn't. If you want to change a record, you use UPDATE. If you want to delete a record, use DELETE. If you want to create a record, use CREATE.