SQL Commands

PostgreSQL

🎯 Purpose	⌨️ Command
Connection test (host)	PGPASSWORD='****' psql -U user -h localhost -p 15432 -d dbname -c 'SELECT 1' PGPASSWORD='****' psql postgres://user@localhost:15432/dbname?sslmode=require -c 'SELECT 1'
Connection test (container)	sudo docker exec -e PGPASSWORD='****' -it postgis-ssl psql -U user -d dbname -c 'SELECT 1'

Common Operations

Functions

	Postgres	MariaDB	SQLite
Today

Visiting

	Postgres	MariaDB	SQLite
List tables		show tables;	.tables

CREATE USER

	Postgres	MariaDB
create user	CREATE USER username WITH PASSWORD 'your_password';
change password

GRANT

	Postgres	MariaDB
on database to user	GRANT ALL PRIVILEGES ON DATABASE operation_center TO db_user;
on table to user	GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO db_user;

SQLite 3

TODO	Command
yyyymmdd to yyyy-mm-dd	SELECT printf('%s-%s-%s', SUBSTR(trading_date,1,4), SUBSTR(trading_date,5,2), SUBSTR(trading_date,7,2) ) FROM short_sell WHERE trading_date='20190605';