Connecting to MySQL: mysql -u user_name -p
Connecting to PostgreSQL: sudo -u postgres psql
| Description | MySQL command | PostgreSQL equivalent |
|---|---|---|
| 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; |
PostgreSQL (psql) meta-commands
\?opens the command overview\dlists things:\dulists users,\dtlists tables etc
\d ( table, view, index, sequence, foreign table) = \dtvsE
\conninfo
Informational
(options: S = show system objects, + = additional detail)
\d[S+] list tables, views, and sequences
\d[S+] NAME describe table, view, sequence, or index
\da[S] [PATTERN] list aggregates
\db[+] [PATTERN] list tablespaces
\dc[S+] [PATTERN] list conversions
\dC[+] [PATTERN] list casts
\dd[S] [PATTERN] show object descriptions not displayed elsewhere
\ddp [PATTERN] list default privileges
\dD[S+] [PATTERN] list domains
\det[+] [PATTERN] list foreign tables
\des[+] [PATTERN] list foreign servers
\deu[+] [PATTERN] list user mappings
\dew[+] [PATTERN] list foreign-data wrappers
\df[antw][S+] [PATRN] list [only agg/normal/trigger/window] functions
\dF[+] [PATTERN] list text search configurations
\dFd[+] [PATTERN] list text search dictionaries
\dFp[+] [PATTERN] list text search parsers
\dFt[+] [PATTERN] list text search templates
\dg[+] [PATTERN] list roles
\di[S+] [PATTERN] list indexes
\dl list large objects, same as \lo_list
\dL[S+] [PATTERN] list procedural languages
\dm[S+] [PATTERN] list materialized views
\dn[S+] [PATTERN] list schemas
\do[S] [PATTERN] list operators
\dO[S+] [PATTERN] list collations
\dp [PATTERN] list table, view, and sequence access privileges
\drds [PATRN1 [PATRN2]] list per-database role settings
\ds[S+] [PATTERN] list sequences
\dt[S+] [PATTERN] list tables
\dT[S+] [PATTERN] list data types
\du[+] [PATTERN] list roles
\dv[S+] [PATTERN] list views
\dE[S+] [PATTERN] list foreign tables
\dx[+] [PATTERN] list extensions
\dy [PATTERN] list event triggers
\l[+] [PATTERN] list databases
\sf[+] FUNCNAME show a function’s definition
\z [PATTERN] same as \dp
References:
http://makandracards.com/makandra/18579-postgresql-cheat-sheet-for-mysql-lamers
http://www.postgresql.org/docs/9.2/static/app-psql.html
