PostgreSQL for MySQL users

I've been a long time user of MySQL, and despite having had some frustrations I've been pretty happy with MySQL. I'm comfortable with it, I know where its weaknesses lie and where it's strong. I've never really had to look at alternatives.

This all changed for a project that has been taking up all of my spare time lately, Geomium, a location-based communication tool. MySQL's geospacial support is almost completely non-existant. PostgreSQL, on the other had, when combined with the PostGIS extension, has fantastic support. So PostgreSQL was what the project needed. I thought it'd be fairly easy to transition from MySQL to PostgresSQL but the command line clients of these two RDBMSes are very different. Here are some of the things I've learnt.

MySQL PostgreSQL Description
mysql -u<user> -p<password <database> psql -U<user> -P<password> <database> Start command line client, and connect to <database>
SHOW DATABASES; \l Show available databases
SHOW TABLES; \dt Show available tables
USE <database>; \c <database> Connect to <database>
DESCRIBE <table>; \d <table> Describe <table> structure
SHOW FULL PROCESSLIST; SELECT * FROM pg_stat_activity; Show all running queries
exit (or quit or \q) \q Quit the client

A few other things to note: PostgreSQL only supports single quotes in queries not double quotes, so

SELECT * FROM table WHERE column = "value"

would need to be rewritten as

SELECT * FROM table WHERE column = 'value'

PostgreSQL also defaults to being case-sensitive, whereas MySQL defaults to case-insensitive, so you might want to modify your queries to something like

SELECT * FROM table WHERE lower(column) = 'value'

although this can prevent an index on "column" from being used.

At first I thought PostgreSQL didn't support some of the handy MySQL date functions (such as NOW() - INTERVAL 1 DAYS), but it turns out that it does support them, and more. It's just fussier about the format. For details see the datetime documentation.

Posted on 27 Feb 2010
If you enjoyed reading this post you might want to follow @coderholic on twitter or browse though the full blog archive.