MySQL and PostgreSQL identical commands

Both MySQL and PostgreSQL are databases which are used. There are many similar commands and functionalities used in both with some differences. Users working with both are always confused with the equivalent commands that are used in both SQL’s. The following is a cheat sheet which will help users:

MySQL (mysql)	Postgres (psql)	Extra Notes
\e	\e	Edit the buffer with external editor. Postgres also allows \e filename which will become the new buffer
\g	\g	Send current query to the server
\h	\h	Gives help — general or specific
\n	\pset pager off	Turns the pager off. For PGSQL, the pager is only used when needed based on number of rows; to force it on, use \pset pager always
\d string	No equivalent	Changes the delimiter
\c	\r	Clears the buffer
\p	\p	Print the current buffer
\q or Quit or exit or [ctrl-c]	\q	Quit the client mysql> or psql>
\r [dbname] [dbhost]	\c [dbname] [dbuser]	Reconnect to server
\s	No equivalent	Status of server. Some of the same info is available from the pg_settings table
\u dbname or  Use [db_name]	\c dbname	Connect to a database or use a different database.
\w	No equivalent	Do not show warnings. 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
\t Stop teeing output to file	No equivalent	However, \o (without any argument) will stop writing to a previously opened outfile
\G	\x	Display results vertically (one column per line). 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
\R string	\set PROMPT1 string	To Change the prompt. Note that the Postgres prompt cannot be reset by omitting an argument. A good prompt to use is:\set PROMPT1 '%n@%`hostname`:%>%R%#%x%x%x '
\P pagername	Environment variable PAGER or PSQL_PAGER	Change the current pager program
\. filename	\i filename	Include a file as if it were typed in.
\T filename	No direct equivalent	Sets the tee output file. Postgres can output to a pipe, so you can do: \o | tee filename
\W	No equivalent	To Show warnings. Postgres always show warnings by default
\?	\?	Help for Internal Commands.
\#	No equivalent	To rebuild tab-completion hash. Not needed in Postgres, as tab-completion in Postgres is always done dynamically
\! command	\! command	To execute a shell command. If no command is given with Postgres, the user is dropped to a new shell (exit to return to psql)
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 or /dt+	Many also use just \d, which lists tables, views, and sequences
desc tablename;	\d tablename	Display information about the given table.
show index from tablename;	\d tablename	Display indexes on the given table. The bottom of the \d tablename output always shows indexes, as well as triggers, rules, and constraints
show triggers from tablename;	\d tablename	Display triggers on the given table. The bottom of the \d tablename output always shows indexes, as well as triggers, rules, and constraints
show databases;	\l	List all databases
No equivalent	\dn	To List all schemas. MySQL does not have the concept of schemas, but uses databases as a similar concept
select version();	select version();	Show backend server version
select now();	select now();	Show current time. Postgres will give fractional seconds in the output
select current_user;	select current_user;	Show the current user.
select database();	select current_database();	Show the current database.
show create table tablename;	No equivalent	Output a CREATE TABLE statement for the given table. The closest you can get with Postgres is to use pg_dump --schema-only -t tablename
show engines;	No equivalent	To List all server engines. 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)
SHOW INDEX FROM some_table; Show indices of some_table (in case of MySQL)	\di Show all indices of database (PostgreSQL)	Display Indices.
mysqldump	pg_dumpall	pg_dumpall is designed to dump all the databases and invokes pg_dump to do it. It can also be used to dump global values like roles and tablespaces. Example with mysql: mysqldump –all-databases > /path/to/file.sql Example with postgres: pg_dumpall > /path/to/file.sql
mysqldump	pg_dump	pg_dump is used for dumping individual databases. Example with mysql: mysqldump mydatabase > /path/to/file.sql Example with postgres: pg_dump mydatabase > /path/to/file.sql
innodb_top	pg_top	innodb_top does not ship with mysql and is a third-party executable. It shows you things like inserts per second, updates per second, transactions per second and gives a good overview as to what is going on with the server pg_top shows similar things but is laid out more similarly to the native linux “top” program.
mysql	psql	These commands allow to access the CLI utility.
Passing query with mysql (-e for execute): mysql -e “select * from table_name;”	Passing query with postgres (-c for command): psql -c “select * from table_name;”	Passing sql into the utility.
show databases;	\l or \list	This lists the databases on the server instance you are connected to or that you have access to.
select * from mysql.user;	select * from pg_user; or\du	Shows all users and their global permissions. Postgres lists the permissions as a comma separated string under a filed called attributes. Mysql shows a boolean value for each of the possible permissions.
show full processlist;	select * from pg_stat_activity;	This will show all the queries that are currently running and how long they have been running for.
show variables;	show all;	This will show all the current values for the variables. Postgres even offers a brief description of what each variable is.
show engine innodb status\G	SELECT * FROM pg_stat_activity; SELECT * FROM pg_stat_database; SELECT * FROM pg_stat_user_tables; SELECT * FROM pg_stat_user_indexes; SELECT * FROM pg_locks;	There is no central place in postgres to get all the information obtained by running show engine innodb status in mysql. There are several queries you can run to get roughly equivalent data though.
show slave status\G	select * from pg_stat_replication; select now() – pg_last_xact_replay_timestamp() AS replication_delay;	Shows replication information. On idle write masters you can errantly see replication report as behind or lagging. This is showing you the current timestamp minus the timestamp of the last item applied on the slave. If the master hasn’t written anything, the slave has not applied anything and can show you that it is behind. It is recommended to use a written timestamp from cron every minute to gauge replication. This achieves 2 things, it will guarantee regular writes to the master which will replicate to the slave, and monitoring can look to the timestamp in a location to know if the server is behind.
stop slave; start slave;	select pg_xlog_replay_pause(); select pg_xlog_replay_resume();	Pause/resume/stop/start replication on the slave node
SHOW FULL TABLES IN database_name WHERE TABLE_TYPE LIKE 'VIEW';	\dv \dv schema_name.*	List views
SHOW [EXTENDED] [FULL] {COLUMNS | FIELDS}     {FROM | IN} tbl_name     [{FROM | IN} db_name]     [LIKE 'pattern' | WHERE expr]	\dt+ \dv+ \d+ table_name Get extended information (add + to any command)	In MySQL, The optional EXTENDED keyword causes the output to include information about hidden columns that MySQL uses internally and are not accessible by users.The optional FULL keyword causes the output to include the column collation and comments, as well as the privileges you have for each column.

Feel free to add in a comment if some commands are not listed. I have not listed the other queries like SELECT, ALTER, DELETE and etc as most of the syntax are similar just follow the documentations mentioned in Postgresql and Mysql websites