I'm pretty familiar with MySQL / MariaDB, so dealing with applications like FusionPBX which use Postgres by default as their backend DB comes as a bit of a challenge.
Fortunately I'm not the only person to wonder "I know how to do this, but how do I do it in Postgres?"
There are several online resources for discovering equivalent commands between the two systems, such as:
I find it interesting that nearly all such resources I've found focus on "how to do stuff in Postgres which you know how to do in MySQL" rather than the other way round. It seems that people more commonly become confident with MySQL and then find some reason they need to interact with Postgres than the other way around.
Anyway, the following is my personal contribution to these resources, based on the things I've found myself most often wanting to do in Postgres and finding it not at all obvious.
To do this: | In MySQL / MariaDB: | In Postgres: |
---|---|---|
Log in and connect to a database | mysql <database name> | psql <database name> |
mysql -u <user name> <database name> | psql -U <user name> <database name> | |
mysql -u <user name> -p <database name> | psql -U <user name> -p <database name> | |
mysql -u <user name> -p<password> <database name> | PGPASSWORD=<password> psql -U <user name> <database name> | |
Run a single DB command | mysql <database name> -e "<command>" | psql <database name> -c "<command>" |
Find out what databases exist | show databases | \l |
Select a database | use <database name> | \c <database name> |
Find out what tables exist | show tables | \dt |
View table fields | show columns from <table name> | \d <table name> |
View a table schema | show create table <table name> | pg_dump --schema-only -t <table name> |
Display running queries | show full processlist | select * from pg_stat_activity |
Export database | mysqldump <database name> > <dump filename> | pg_dump <database name> > <dump filename> |
Export database table | mysqldump <database name> <table name> > <dump filename> | pg_dump <database name> -t <table name> > <dump filename> |
Import database or table | mysql < <dump filename> | psql <database name> < <dump filename> |
Display query results with one field per line | Use \G to terminate the query instead of ; | Use \x to toggle between one row per line and one field per line of output |
Go up
Return to main index.