PostgreSQL Cheat Sheet
Published: 2022-05-11 08:41:13

We gathered a list of handy PostgreSQL commands and queries when working with PostgreSQL databases. Please ping us [@ForestAdmin](https://twitter.com/ForestAdmin) if you’d like to add something to the cheat sheet!

## Getting started with PostgreSQL

Login and enter PostgreSQL command line utility psql

```
$ sudo su - postgres psql
```

List postgres databases

```
\l
```
Connect to postgres database

```
\c databaseName
```
Exit postgreSQL command line utility psql

```
\q
```
## Managing PostgreSQL

Check PostgreSQL version

```
SELECT version();
```

Check if PostgreSQL is installed

```
$ which psql
```

Check if PostgreSQL is running

```
$ pgrep -fa -- -D | grep postgres
```

Restart PostgreSQL on Linux

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

Restart PostgreSQL on OSX

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

Restart PostgreSQL on Windows

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

Change PostgreSQL user password

```
$ sudo -u postgres psql
\password postgres
```

Exit from psql

```
\q
```

## Interacting with databases in PostgreSQL

List postgres databases

```
\l
```
Connect or switch 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
```

Query postgres JSON data

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

## Interacting with tables in PostgreSQL

List postgres tables

```
\dt
```
Describe postgres table

```
\d tableName
```

Create postgres table

```
CREATE TABLE tableName(	
	columnName columnType,
	columnName columnType
);
```

Delete postgres table

```
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

List postgres roles

```
SELECT rolname FROM pg_roles;
```

Create postgres user

```
CREATE USER userName WITH PASSWORD 'userPassword';
```

Delete postgres user

```
DROP USER userName;
```

Change postgres user password

```
ALTER ROLE userName WITH PASSWORD 'userPassword';
```

List 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;
```

List permissions for specific role postgres

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

## ProstgreSQL psql commands recap

`\?` List all available psql commands

`\h COMMAND` Get help on specific command

`\l` List databases

`\c` databaseName Connect to database

`\dt` List tables

`\d` tableName Describe table

`\d+` tableName Describe table with details

`\dn` List schemas

`\df` List functions

`\dv` List views

`\du` List users

`\dy` List events

`\di` List indexes

`\q` Exit

***

*Need to build admin panels or a GUI tool for PostgreSQL? Check out [Forest Admin for PostgreSQ](https://www.forestadmin.com/integrations/postgresql?utm_source=referral&utm_medium=devto&utm_campaign=cheat-sheet-postgresql&utm_content=cheat-sheet-postgresql)L*