====== 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:

  * [[https://sqlconjuror.com/mysql-and-postgresql-equivalent-commands/|SQL Conjuror]]
  * [[https://www.endpointdev.com/blog/2009/12/mysql-and-postgres-command-equivalents/|End Point Dev]]
  * [[https://gist.github.com/cimmanon/0f17b55868ec222ff3b32eb48656aca5|Cimmanon on Github]]
  * [[https://zuut.com/postgres-commands/|Zuut]] (server seems unreliable)

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 =====

  * Note that unlike MySQL / MariaDB, Postgres does not simply allow the root user to connect and then find out what databases exist.
  * Also note that Postgres may not be listening on a local Unix socket, so the plain **psql** command may not work without specifying to connect to localhost: **psql -h 127.0.0.1**
  * Postgres has the irritating habit of displaying the output of commands such as **\dt** using the //less// pager, meaning that (a) you need to scroll screen by screen through the output, and then press **q** once you've finished, and (b) the output then disappears completely, instead of leaving it on screen for you to refer to when typing further commands.
    * To avoid this, turn off paging using the command **\pset pager off**.


----
[[.:|Go up]]\\
Return to [[:|main index]].