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
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_ ;