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.