MySQL vs PostgreSQL Command Comparison

A comparison table of MySQL vs PostgreSQL commands

|*Item|*MySQL|*PostgreSQL|
|Connect to DB|mysql -h hostname -u user -ppasswd |psql -h hostname -U user |
|List databases|SHOW DATABASES;|\l|
|Switch database|use db_name|psql db_name|
|Create database|CREATE DATABASE db_name;|CREATE DATABASE db_name;|
|List users|SELECT * FROM mysql.user;|SELECT * FROM pg_shadow;|
|Add user|CREATE USER username IDENTIFIED BY [PASSWORD] ‘password’|CREATE ROLE username WITH LOGIN PASSWORD ‘password’|
|Change user password|SET PASSWORD FOR user = PASSWORD(‘password’);|ALTER USER username password ‘password’
ALTER USER username with encrypted password ‘password’;|
|Grant all privileges|GRANT ALL PRIVILEGES ON DATABASE database TO username;|GRANT ALL PRIVILEGES ON DATABASE database TO username;|
|List tables|SHOW TABLES|\d|
|List fields|SHOW CLOMNS FROM tbl_name
SHOW COLOMNS FROM tbl_name FOM db_name|\d tbl_name|
|Run SQL| SELECT * FROM tbl_name;
UPDATE tbl_name SET column=’‘;
DELETE FROM tbl_name; |SELECT * FROM tbl_name;
UPDATE tbl_name SET column=’‘;
DELETE FROM tbl_name;|
|Disconnect from DB|exit;|\q|