PostgreSQL cheat sheet for MySQL lamers

Updated . Posted . Visible to the public.

So you're switching to PostgreSQL from MySQL? Here is some help...

General hints on PostgreSQL

  • \? opens the command overview
  • \d lists things: \du lists users, \dt lists tables etc

Command comparison

Description MySQL command PostgreSQL equivalent
Connect to the database mysql -u $USERNAME -p sudo -u postgres psql
Show databases SHOW DATABASES; \l[ist]
Use/Connect to a database named 'some_database' USE some_database; \c some_database
Show tables/relations within one database SHOW TABLES; \dt
Show table details (columns, types) DESCRIBE some_table; \d+ some_table
Show indices of some_table (in case of MySQL) and all indices of database (PostgreSQL) SHOW INDEX FROM some_table; \di
Create user that can create databases CREATE USER harry IDENTIFIED BY 'foo'; CREATE ROLE username WITH createdb LOGIN [PASSWORD 'password'];
Change password of an existing user ... ALTER ROLE username WITH PASSWORD 'password';
Grants access to a database. GRANT ALL PRIVILEGES ON database.* TO username@localhost; GRANT ALL PRIVILEGES ON DATABASE database TO username;
Grants access to create databases. ? ALTER USER username CREATEDB;
Expanded table formatting mode (pretty print) YOUR QUERY\G; \x on

Further reading

Thomas Eisenbarth
Last edit
Dominik Schöler
License
Source code in this card is licensed under the MIT License.
Posted by Thomas Eisenbarth to makandra dev (2013-09-26 15:35)