mysql-postgresql.md

Cheatsheet: MySQL vs PostgreSQL commands

Description	MySQL	PostgreSQL
Connect to database server	mysql -u username -p	psql -U username
Import a database	mysql -u username -p databaseName < data.sql	psql -U username databaseName < data.sql
List databases	SHOW databases;	\l
Use/Connect to datbase with name	USE databaseName;	\c databaseName
List tables	SHOW tables;	\dt
List users	SELECT user, host FROM mysql.user;	\du
Create a user	CREATE USER username IDENTIFIED BY 'password';	CREATE ROLE username WITH database LOGIN [PASSWORD 'password']
Change password	ALTER USER 'username'@'localhost' IDENTIFIED BY 'newPassword'; FLUSH PRIVILEGES;	ALTER ROLE username WITH PASSWORD 'newPassword' VALID UNTIL 'infinity';
Grant user access to database	GRANT ALL PRIVILEGES ON database.* TO username@localhost;	GRANT ALL PRIVILEGES ON DATABASE database TO username;
Show permissions	SHOW GRANTS FOR 'username'@'localhost';
Allow connections from any IP address	'username'@'%'
Change host for database user	UPDATE mysql.user SET Host='%' WHERE Host='localhost' AND User='username'; UPDATE mysql.db SET Host='%' WHERE Host='localhost' AND User='username'; FLUSH PRIVILEGES;
List table engine	SELECT table_name, table_schema, engine FROM information_schema.tables WHERE table_schema = 'yourDatabaseName';
Shrink size of table	OPTIMIZE TABLE 'tableName';	VACUUM FULL 'tableName'
Execute SQL from command line	mysql -u username -p dbname --execute='SHOW TABLES';
Restart SQL server (Ubuntu)	sudo service mysql restart
List if field contains numeric	SELECT * FROM tableName WHERE columnName REGEXP '^[0-9]+$';
Display number of connections	SHOW STATUS WHERE variable_name = 'Threads_connected';