[LRUG] Reasons for Postgres (was: Migrating SQLlite to MySQL)

[Murray Steele]

2009/11/16 Jocke Selin <jocke at selincite.com>:
> On 16 Nov 2009, at 16:52, Sam Stokes wrote:
>> Now that you've brought it up :)  Would you be able to list a few things that you like about Postgres, or even reasons to use Postgres over MySQL?
>>
> One more is that you can use transactions for anything in PostgreSQL, inlcuding DDL (Data Description Language; CREATE, DROP, ALTER, etc). - this allows you to create "patch scripts" that you can run on a more-or-less live server to migrate your changes over. If everything works in a SQL script you simply issue COMMIT and your changes are there. (and I just saw that Tom Stuart agrees on this one :) )

Transactional DDL is literally the only thing I miss from my MS SQL
Server days.  Since I moved jobs and have been using MySQL practically
every migration I've written that's involved massaging data as well as
the schema has broken at least once and left me with a dev DB gunned
down halfway along it's migratory path*. It's enormously painful to
mess about with the down to try and get things back (and calling
scorched earth on the DB and starting again from afresh means I'm not
really convinced that my migration works as there was no data for it
to massage).

There may be other things that PG does well, but "everything can be
transactional" is a *huge* win.

Muz

* I admit that this says more about my (lack of) skills when writing
migrations than it does failings of MySQL, but still...