Mapping MySQL Commands and Features to PostgreSQL psql Equivalents

General Differences

PostgreSQL's psql command line accepts many arguments similar to MySQL's client, e.g., -h for help.

Here are some obvious differences:

MySQL -u corresponds to psql -U (uppercase U) to specify the database user.

MySQL -P (port) corresponds to psql -p (lowercase p) to specify the port.

MySQL -p for password; psql does not allow password on the command line; use a .pgpass file instead.

SQL‑level Equivalents

What is MySQL's SHOW TABLES in PostgreSQL?

In psql:

\d table_name

Without a table name:

\d

Shows all tables.

What is MySQL's SHOW CREATE TABLE in PostgreSQL?

The simplest way:

pg_dump -h db_ip_address -U db_user -t table_name -s db_name

What is MySQL's SHOW DATABASES in PostgreSQL?

In psql:

\l

Or from the shell:

psql -h db_ip_address -U db_superuser -l

What is MySQL's USE DATABASE in PostgreSQL?

In psql:

\c db_name

Or from the shell with -d:

psql -h db_ip_address -U db_superuser -d db_name

How to emulate MySQL's AUTO_INCREMENT in PostgreSQL?

PostgreSQL provides the SERIAL type, equivalent to MySQL's INT AUTO_INCREMENT.

create table (
    id serial
);

There is also BIGSERIAL for 8‑byte integers.

What is MySQL's SHOW FULL PROCESSLIST in PostgreSQL?

select * from pg_stat_activity;

pg_stat_activity is a system view, so you can filter or sort, e.g.:

select client_addr, count(1)
from pg_stat_activity
group by client_addr
order by count(1) desc;

What is MySQL's SHOW GLOBAL VARIABLES in PostgreSQL?

Run in psql:

show all;

Command‑line Level

What is MySQL's -e option in psql?

psql -h db_ip_address -U dbuser -d db_name -c $'select * from table'

To output tab‑separated data, use PostgreSQL's COPY command:

psql -h db_ip_address -U dbuser -d db_name \
-c $'copy (select * from table) to stdout' > some_file

psql supports full piping, so you can redirect results to any file or further processing.

Function Level

What is MySQL's REGEXP (or regexp_replace) equivalent in PostgreSQL?

PostgreSQL does not have a direct UTF‑8 aware regexp_replace function; MySQL's REGEXP operator supports ASCII regex matching but not replacement. You would need to write your own function or use a user‑defined function (UDF).