PostgreSQL Cheat Sheet – DEV Community

We gathered a listing of helpful PostgreSQL instructions and queries when working with PostgreSQL databases. Please ping us @ForestAdmin for those who’d like so as to add one thing to the cheat sheet!

Getting began with PostgreSQL

Login and enter PostgreSQL command line utility psql

$ sudo su - postgres psql

Record postgres databases

l

Hook up with postgres database

c databaseName

Exit postgreSQL command line utility psql

q

Managing PostgreSQL

Test PostgreSQL model

SELECT model();

Test if PostgreSQL is put in

$ which psql

Test if PostgreSQL is working

$ pgrep -fa -- -D | grep postgres

Restart PostgreSQL on Linux

# systemctl restart postgresql
// or
# service postgresql restart
// or
# /and so forth/init.d/postgresql restart
// or
# /sbin/service postgresql restart

Restart PostgreSQL on OSX

# brew companies restart postgres
// or
# pg_ctl -D /usr/native/var/postgres -l /usr/native/var/postgres/server.log restart

Restart PostgreSQL on Home windows

Winkey + R
Sort "companies.msc"
Click on "restart"

Change PostgreSQL person password

$ sudo -u postgres psql
password postgres

Exit from psql

q

Interacting with databases in PostgreSQL

Record postgres databases

l

Join or swap to postgres database

c databaseName

Create new postgres database

CREATE DATABASE databaseName

Delete postgres database

DROP DATABASE databaseName

Rename postgres database

ALTER DATABASE old_databaseName TO new_databaseName

Question postgres JSON information

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

Interacting with tables in PostgreSQL

Record postgres tables

dt

Describe postgres desk

d tableName

Create postgres desk

CREATE TABLE tableName( 
    columnName columnType,
    columnName columnType
);

Delete postgres desk

DROP TABLE tableName CASCADE

Backup and restore PostgreSQL database

Backup postgres database

$ pg_dump -d databaseName -U userName > backupFile

Restore postgres database

psql databaseName < backupFile

Managing roles and permissions in PostgreSQL

Record postgres roles

SELECT rolname FROM pg_roles;

Create postgres person

CREATE USER userName WITH PASSWORD 'userPassword';

Delete postgres person

DROP USER userName;

Change postgres person password

ALTER ROLE userName WITH PASSWORD 'userPassword';

Record all assigned roles postgres

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 permissions on postgres database

GRANT ALL PRIVILEGES ON DATABASE databaseName TO userName;

Grant connection permissions on postgres database

GRANT CONNECT ON DATABASE databaseName TO userName;

Record permissions for particular function postgres

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

ProstgreSQL psql instructions recap

? Record all out there psql instructions

h COMMAND Get assistance on particular command

l Record databases

c databaseName Hook up with database

dt Record tables

d tableName Describe desk

d+ tableName Describe desk with particulars

dn Record schemas

df Record features

dv Record views

du Record customers

dy Record occasions

di Record indexes

q Exit

Must construct admin panels or a GUI instrument for PostgreSQL? Try Forest Admin for PostgreSQL