MySQL and Postgres command equivalents (mysql vs psql)

From: http://blog.endpoint.com/2009/12/mysql-and-postgres-command-equivalents.html

Posted by Greg Sabino Mullane |December 24, 2009

 

 

Users toggling between MySQL and Postgres are often confused by the equivalent commands to accomplish basic tasks. Here's a chart listing some of the differences between the command line client for MySQL (simply called mysql), and the command line client for Postgres (called psql).

MySQL (using mysql) Postgres (using psql) Notes

\c Clears the buffer	\r (same)
\d string Changes the delimiter	No equivalent
\e Edit the buffer with external editor	\e (same)	Postgres also allows \e filename which will become the new buffer
\g Send current query to the server	\g (same)
\h Gives help - general or specific	\h (same)
\n Turns the pager off	\pset pager off (same)	The pager is only used when needed based on number of rows; to force it on, use \pset pager always
\p Print the current buffer	\p (same)
\q Quit the client	\q (same)
\r [dbname] [dbhost] Reconnect to server	\c [dbname] [dbuser] (same)
\s Status of server	No equivalent	Some of the same info is available from the pg_settings table
\t Stop teeing output to file	No equivalent	However, \o (without any argument) will stop writing to a previously opened outfile
\u dbname Use a different database	\c dbname (same)
\w Do not show warnings	No equivalent	Postgres always shows warnings by default
\C charset Change the charset	\encoding encoding Change the encoding	Run \encoding with no argument to view the current one
\G Display results vertically (one column per line)	\x (same)	Note that \G is a one-time effect, while \x is a toggle from one mode to another. To get the exact same effect as \G in Postgres, use \x\g\x   However, you can enable \G mysql style formatting on a per-query basis in psql by putting the following in ~/.psqlrc(windows: %appData%/Roaming/postgresql/psqlrc.conf), and replace ";" with ":G" at the end of each query: \set G '\\set QUIET 1\\x\\g\\x\\set QUIET 0'
\P pagername Change the current pager program	Environment variable PAGER or PSQL_PAGER
\R string Change the prompt	\set PROMPT1 string (same)	Note that the Postgres prompt cannot be reset by omitting an argument. A good prompt to use is:\set PROMPT1 '%n@%m:%> %/%R%#%x%x%x '
\T filename Sets the tee output file	No direct equivalent	Postgres can output to a pipe, so you can do: \o | tee filename
\W Show warnings	No equivalent	Postgres always show warnings by default
\? Help for internal commands	\? (same)
\# Rebuild tab-completion hash	No equivalent	Not needed, as tab-completion in Postgres is always done dynamically
\! command Execute a shell command	\! command (same)	If no command is given with Postgres, the user is dropped to a new shell (exit to return to psql)
\. filename Include a file as if it were typed in	\i filename (same)
Timing is always on	\timing Toggles timing on and off
No equivalent	\t Toggles 'tuple only' mode	This shows the data from select queries, with no headers or footers
show tables; List all tables	\dt (same)	Many also use just \d, which lists tables, views, and sequences
desc tablename; Display information about the given table	\d tablename (same)
show index from tablename; Display indexes on the given table	\d tablename (same)	The bottom of the \d tablename output always shows indexes, as well as triggers, rules, and constraints
show triggers from tablename; Display triggers on the given table	\d tablename (same)	See notes on show index above
show databases; List all databases	\l (same)
No equivalent	\dn List all schemas	MySQL does not have the concept of schemas, but uses databases as a similar concept
select version(); Show backend server version	select version(); (same)
select now(); Show current time	select now(); (same)	Postgres will give fractional seconds in the output
select current_user; Show the current user	select current_user; (same)
select database(); Show the current database	select current_database(); (same)
show create table tablename; Output a CREATE TABLE statement for the given table	No equivalent	The closest you can get with Postgres is to use pg_dump --schema-only -t tablename
show engines; List all server engines	No equivalent	Postgres does not use separate engines
CREATE object ... Create an object: database, table, etc.	CREATE object ... Mostly the same	Most CREATE commands are similar or identical. Lookup specific help on commands (for example: \h CREATE TABLE)

If there are any commands not listed you would like to see, or if there are errors in the above, please let me know. There are differences in how you invoke mysql and psql, and in the flags that they use, but that's a topic for another day.

Updates: Added PSQL_PAGER and \o |tee filename, thanks to the Davids in the comments section. Added \t back in, per Joe's comment.