title: PostgreSQL background: bg-[#3d6488] tags: - DB - RDBMS categories: - Database date: 2021-01-11 14:19:24 intro: |

The PostgreSQL cheat sheet provides you with the common PostgreSQL commands and statements.

Getting Started

Getting started

Switch and connect ```shell script $ sudo -u postgres psql

List all databases
```shell script
postgres=# \l

Connect to the database named postgres ```shell script postgres=# \c postgres

Disconnect
```shell script
postgres=# \q
postgres=# \!

psql commands {.col-span-2}

{.show-header}

Getting help

Run in PostgreSQL console

PostgreSQL Working

Recon

Show version

SHOW SERVER_VERSION;

Show system status ```sql {.wrap} \conninfo

Show environmental variables
```sql  {.wrap}
SHOW ALL;

List users ```sql {.wrap} SELECT rolname FROM pg_roles;

Show current user
```sql  {.wrap}
SELECT current_user;

Show current user's permissions

\du

Show current database ```sql {.wrap} SELECT current_database();

Show all tables in database
```sql  {.wrap}
\dt

List functions ```sql {.wrap} \df

### Databases

List databases
```sql  {.wrap}
\l

Connect to database ```sql {.wrap} \c

Show current database
```sql  {.wrap}
SELECT current_database();

Create database ```sql {.wrap} CREATE DATABASE WITH OWNER ;

[Drop database](http://www.postgresql.org/docs/current/static/sql-dropdatabase.html)
```sql {.wrap}
DROP DATABASE IF EXISTS <database_name>;

Rename database ```sql {.wrap} ALTER DATABASE RENAME TO ;

### Tables

List tables, in current db
```sql  {.wrap}
\dt

SELECT table_schema,table_name FROM information_schema.tables ORDER BY table_schema,table_name;

List tables, globally ```sql {.wrap} \dt ..

SELECT * FROM pg_catalog.pg_tables

List table schema
```sql  {.wrap}
\d <table_name>
\d+ <table_name>

SELECT column_name, data_type, character_maximum_length
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = '<table_name>';

Create table ```sql {.wrap} CREATE TABLE ( , );

Create table, with an auto-incrementing primary key
```sql  {.wrap}
CREATE TABLE <table_name> (
  <column_name> SERIAL PRIMARY KEY
);

Delete table ```sql {.wrap} DROP TABLE IF EXISTS CASCADE;

### Permissions
Become the postgres user, if you have permission errors
```shell
sudo su - postgres
psql

Grant all permissions on database ```sql {.wrap} GRANT ALL PRIVILEGES ON DATABASE TO ;

Grant connection permissions on database
```sql  {.wrap}
GRANT CONNECT ON DATABASE <db_name> TO <user_name>;

Grant permissions on schema ```sql {.wrap} GRANT USAGE ON SCHEMA public TO ;

Grant permissions to functions
```sql  {.wrap}
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO <user_name>;

Grant permissions to select, update, insert, delete, on a all tables ```sql {.wrap} GRANT SELECT, UPDATE, INSERT ON ALL TABLES IN SCHEMA public TO ;

Grant permissions, on a table
```sql  {.wrap}
GRANT SELECT, UPDATE, INSERT ON <table_name> TO <user_name>;

Grant permissions, to select, on a table ```sql {.wrap} GRANT SELECT ON ALL TABLES IN SCHEMA public TO ;

### Columns

[Add column](http://www.postgresql.org/docs/current/static/sql-altertable.html)
```sql  {.wrap}
ALTER TABLE <table_name> IF EXISTS
ADD <column_name> <data_type> [<constraints>];

Update column ```sql {.wrap} ALTER TABLE IF EXISTS ALTER TYPE [];

Delete column
```sql  {.wrap}
ALTER TABLE <table_name> IF EXISTS
DROP <column_name>;

Update column to be an auto-incrementing primary key ```sql {.wrap} ALTER TABLE ADD COLUMN SERIAL PRIMARY KEY;

Insert into a table, with an auto-incrementing primary key
```sql  {.wrap}
INSERT INTO <table_name>
VALUES (DEFAULT, <value1>);


INSERT INTO <table_name> (<column1_name>,<column2_name>)
VALUES ( <value1>,<value2> );

Data

[Select](http://www.postgresql.org/docs/current/static/sql-select.html] all data ```sql {.wrap} SELECT * FROM ;

Read one row of data
```sql  {.wrap}
SELECT * FROM <table_name> LIMIT 1;

Search for data ```sql {.wrap} SELECT * FROM WHERE = ;

[Insert](http://www.postgresql.org/docs/current/static/sql-insert.html) data
```sql  {.wrap}
INSERT INTO <table_name> VALUES( <value_1>, <value_2> );

Update data ```sql {.wrap} UPDATE SET = , = WHERE = ;

[Delete](http://www.postgresql.org/docs/current/static/sql-delete.html) all data
```sql  {.wrap}
DELETE FROM <table_name>;

Delete specific data ```sql {.wrap} DELETE FROM WHERE = ;

### Users

List roles
```sql  {.wrap}
SELECT rolname FROM pg_roles;

Create user ```sql {.wrap} CREATE USER WITH PASSWORD '';

[Drop user](http://www.postgresql.org/docs/current/static/sql-dropuser.html)
```sql  {.wrap}
DROP USER IF EXISTS <user_name>;

Alter user password ```sql {.wrap} ALTER ROLE WITH PASSWORD '';

### Schema
List schemas
```sql  {.wrap}
\dn

SELECT schema_name FROM information_schema.schemata;

SELECT nspname FROM pg_catalog.pg_namespace;

Create schema ```sql {.wrap} CREATE SCHEMA IF NOT EXISTS ;

[Drop schema](http://www.postgresql.org/docs/current/static/sql-dropschema.html)
```sql  {.wrap}
DROP SCHEMA IF EXISTS <schema_name> CASCADE;

PostgreSQL Commands

Tables

Query buffer

Informational {.row-span-4}

S: show system objects, +: additional detail

Connection

Formatting

Input/Output

Variables

Misc

Large Objects

• \lo_export LOBOID FILE
• \lo_import FILE [COMMENT]
• \lo_list
• \lo_unlink LOBOID

Miscellaneous

Backup

Use pg_dumpall to backup all databases ```shell script $ pg_dumpall -U postgres > all.sql

Use pg_dump to backup a database
```shell script
$ pg_dump -d mydb -f mydb_backup.sql

•   -a   Dump only the data, not the schema
•   -s   Dump only the schema, no data
•   -c   Drop database before recreating
•   -C   Create database before restoring
•   -t   Dump the named table(s) only
•   -F   Format (c: custom, d: directory, t: tar) {.marker-none}

Use pg_dump -? to get the full list of options

Restore

Restore a database with psql ```shell script $ psql -U user mydb < mydb_backup.sql

Restore a database with pg_restore
```shell script
$ pg_restore -d mydb mydb_backup.sql -c

• &nbsp; -U &nbsp; Specify a database user
• &nbsp; -c &nbsp; Drop database before recreating
• &nbsp; -C &nbsp; Create database before restoring
• &nbsp; -e &nbsp; Exit if an error has encountered
• &nbsp; -F &nbsp; Format (c: custom, d: directory, t: tar, p: plain text sql(default)) {.marker-none}

Use pg_restore -? to get the full list of options

Remote access

Get location of postgresql.conf ```shell script $ psql -U postgres -c 'SHOW config_file'

Append to postgresql.conf
```shell script
listen_addresses = '*'

Append to pg_hba.conf (Same location as postgresql.conf) ```shell script host all all 0.0.0.0/0 md5 host all all ::/0 md5

Restart PostgreSQL server
```shell script
$ sudo systemctl restart postgresql

Import/Export CSV

Export table into CSV file ```shell script \copy table TO '' CSV \copy table(col1,col1) TO '' CSV \copy (SELECT...) TO '' CSV

Import CSV file into table
```shell script
\copy table FROM '<path>' CSV
\copy table(col1,col1) FROM '<path>' CSV

See also: Copy

Also see

• Posgres-cheatsheet (gist.github.com)