This article shows a list of basic commands on MySQL and their corresponding versions for PostgreSQL.

Connecting to a database

MySQL

$ mysql -u root -p

Postgres

$ su pgsql
$ psql

Or using a different user:

$ psql dbname

If you can’t connect because you don’t know what databases are available, try any of these:

psql -l
psql postgres
psql template1

The postgres database is a default database meant for use by users, utilities and third party applications read more.

Listing available databases

mysql> show databases;

postgres=# \l

Listing schemas

This only applies to Postgres. MySQL does not have schemas.

postgres=# \dn

Switching databases

mysql> use ocdp;

postgres=# \c ocdp

Showing available tables

mysql> show tables;

ocdp=# \dt

Creating a database

mysql> create database foo;

postgres=# create database foo;

Dropping a database

mysql> drop database foo;

postgres=# drop database foo;

Creating a schema

postgres=# create schema myschema;

Dropping a schema

postgres=# drop schema myschema;

Drop a schema including all its objects:

postgres=# drop schema myschema cascade;

Creating a table

mysql> create table company (...);

postgres=# create table myschema.company (...);

Listing tables in a schema

postgres=# \dt myschema.*

list tables in all schemas

postgres=# \dt *.*

Quitting the database shell

mysql> quit

postgres=# \q

Setting permissions to a database

create user some_user with password 'password';
grant all privileges on database postgres to some_user;
psql postgres

Setting restrictive permissions to a schema

More about this topic:

GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA some_schema_ TO some_user_;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA some_schema_ TO some_user_;
GRANT USAGE ON SCHEMA some_schema_ TO some_user_ ;