There have been some reactions about my MySQL – PostgreSQL article that I wrote recently.
I received a kind email from Magnus Hagandar, who is one of PostgreSQL’s developers (the PostgreSQL website states: “Has done major work on the Win32 port and wrote much of the Win32 Installer.”). He answered some of my unclear questions about Postgresql (and allowed me to post it here in my blog, so here it is):
Stumbled across
http://db4free.blogspot.com/2005/12/mysql-postgresql.html, thought I
should answer two questions for you. You get it in mail since you rblog
doesn’t permit anon postings and I don’t have an account 🙂
> When a transaction is started and you write a SQL statement that has a
> syntax error in it, PostgreSQL rolls back the transaction immediately,
> while MySQL shows an error message
Check the ON_ERROR_ROLLBACK parameter
(http://www.postgresql.org/docs/8.1/static/app-psql.html). This is for
psql – if you want it in your own custom app, just use a SAVEPOINT.
(this parameter will make psql use SAVEPOINTs automatically)
> MySQL has an ‘enum’ data type. I haven’t found a similar data type in
> PostgreSQL, however, ‘enum’s can be emulated with check constraints.
If you have many possible values in your enum, you really should go with
a separate table and a foreign key. That keeps it inline with the
relational model as well 🙂
> I haven’t yet figured out where PostgreSQL stores this data and I
> think it’s only possible to use the SQL commands to set the permissions
> accordingly.
They’re stored in a system table, normally pg_class (but for a schema,
in pg_namespace etc). See
http://www.postgresql.org/docs/8.1/static/catalogs.html, and the
subpages.
You’re never supposed to hack these using anything other than
GRANT/REVOKE, but you can.
Hope it helps some 🙂
//
Magnus
I also received a mail from Carsten Pederson (he’s the Certification Manager at MySQL AB) and corrected one of my statements. Extended inserts (those which allow you to insert more than one row at a time) are not an extension to MySQL, but SQL standard. It’s one of the few SQL standard features that PostgreSQL doesn’t support at the moment.
There are three more differences that I forgot in my last article, so here they are:
- With MySQL it’s possible to insert new columns somewhere in the middle of an existing table, while PostgreSQL adds new columns always after all columns.
- MySQL has a REPLACE command that doesn’t exist in PostgreSQL.
- PostgreSQL has an additional “layer” called schema (so it’s database – schema – tables, unlike in MySQL it is database – tables).
Of course there exist a lot more, to count them all up would probably be enough to fill a book. But it’s interesting to see how two different products have very much in common, but still a lot of differences and it’s real fun to me to find out new things on both MySQL and PostgreSQL. The more I work with PostgreSQL, the more I like it, but the nice thing about it is that it doesn’t affect the way I like MySQL :-).