Table of Contents

Doing stuff in Postgres

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.

Commands

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

Irritations


Go up
Return to main index.