MySQL and Postgres Commands

A comparison of commands in MySQL and Postgres.

Action	MySQL	Postgres
Describe table	desc $TABLE	\dS $TABLE (also works for indexes)
List tables	show tables;	\dt
List users	show users;	\dg
List databases	show databases;	\l
List triggers	show triggers\G;	select * from pg_trigger;
Ending session	exit;	\q

 

List indexes: Postgres: \di

List functions: Postgres: \df

List sequences: Postgres: \ds

List extensions: Postgres: \dx

Quick and dirty way to create a user and a database:

In Postgres:

sudo -u postgres psql postgres

create user collab_dev with password 'dev-word-to-pass001';
create database spring_security_002_dev_db with owner collab_dev;
grant all privileges on database spring_security_002_dev_db to collab_dev;

To log in:

export PGPASSWORD=dev-word-to-pass001
psql -h localhost -U collab_dev -d collab_todo_dev_db

User and database recipe for MySQL:

create database collab_todo_dev;
grant all privileges on collab_todo_dev.* to 'collab-dev-admin'@'%' identified by 'dev-word-to-pass' with grant option;
grant all privileges on collab_todo_dev.* to 'collab-dev-admin'@'localhost' identified by 'dev-word-to-pass' with grant option;
flush privileges;
commit;

To log into the database via the command line:

mysql --user collab-dev-admin --password=dev-word-to-pass --database=collab_todo_dev

To load a database from a dump file:

mysql --user=collab-dev-admin --password=dev-word-to-pass < /home/ericm/github/James-Admin-Web-App/db.schema.test.txt

Postgres SQL commands – Postgres Meta-Commands –

MySQL documentation –

There is also a comparison of PostgreSQL, MySQL and SQLite at the Hyperpolyglot site.