PostgreSQL Cheat Sheet

$ sudo su - postgres psql

\l

\c databaseName

\q

 SELECT version();

$ which psql

$ pgrep -fa -- -D | grep postgres

# systemctl restart postgresql // or # service postgresql restart // or # /etc/init.d/postgresql restart // or # /sbin/service postgresql restart

# brew services restart postgres // or # pg_ctl -D /usr/local/var/postgres -l /usr/local/var/postgres/server.log restart 

Winkey + R Type "services.msc" Click "restart"

$ sudo -u postgres psql \password postgres

\q

\l

\c databaseName

CREATE DATABASE databaseName

DROP DATABASE databaseName

ALTER DATABASE old_databaseName TO new_databaseName

SELECT * FROM mytable WHERE myfield @> '{"key1":"valueA"}'

\dt

\d tableName

CREATE TABLE tableName( columnName columnType, columnName columnType );

DROP TABLE tableName CASCADE

$ pg_dump -d databaseName -U userName > backupFile

psql databaseName < backupFile

SELECT rolname FROM pg_roles;

CREATE USER userName WITH PASSWORD 'userPassword';

DROP USER userName;

ALTER ROLE userName WITH PASSWORD 'userPassword';

SELECT r.rolname, r.rolsuper, r.rolinherit, r.rolcreaterole, r.rolcreatedb, r.rolcanlogin, r.rolconnlimit, r.rolvaliduntil, ARRAY(SELECT b.rolname FROM pg_catalog.pg_auth_members m JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid) WHERE m.member = r.oid) as memberof, r.rolreplication FROM pg_catalog.pg_roles r ORDER BY 1;

GRANT ALL PRIVILEGES ON DATABASE databaseName TO userName;

GRANT CONNECT ON DATABASE databaseName TO userName;

SELECT table_catalog, table_schema, table_name, privilege_type FROM information_schema.table_privileges WHERE grantee = userName ORDER BY table_name;