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

Connecting to a database

MySQL

1$ mysql -u root -p

Postgres

1$ psql -h <host> -U <username> <database-name>

Try the following if you don’t know the name of the available databases:

1psql -h <host> -U <username> -l

Some installations add your current user to the list of allowed users to the database. In that case you can connect without specifying a username. Some others create a postgres or pgsql user, so as an alternative, you can su into that user and then run psql:

1$ su pgsql
2$ psql

The default 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 in current database

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;

postgres=# \dt

Describing a table

mysql> describe table;

postgres=# \d table

You can get more details about a table by using the \d+ command.

Creating a database

1mysql> create database foo;

1postgres=# create database foo;

Dropping a database

1mysql> drop database foo;

1postgres=# drop database foo;

Creating a schema

1postgres=# create schema myschema;

Dropping a schema

1postgres=# drop schema myschema;

Drop a schema including its objects:

1postgres=# drop schema myschema cascade;

Creating a table

1mysql> create table my_table (...);

1postgres=# create table my_schema.my_table (...);

Listing tables in a schema

postgres=# \dt myschema.*

list tables in all schemas

postgres=# \dt *.*

Quitting the database shell

mysql> quit

postgres=# \q

Creating a user

1create user some_user with encrypted password 'password';

Granting permissions to a database

1grant all privileges on database my_database to some_user;

Granting permissions to a schema

1grant all privileges on schema my_schema to some_user;

Granting permissions to all tables

1grant all privileges on all tables in schema my_schema to some_user;

Setting restrictive permissions to a schema

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

More about this topic.