From MySQL to Postgres - Useful commands
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
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_ ;