PostgreSQL 🐘 Cheatsheet

Table of Content

Database
Table
User
How To Delete Duplicate Rows in PostgreSQL
Show active sessions on a database
Replicate Databases over Servers with pg_dump command
Change Table \& Sequence ownership
CSV \& JSON Gzip Data Dump
Timezone

Database

Command	Description
`\l+`	List all databases
`\c database_name`	Connect to database_name
`CREATE DATABASE database_name WITH TEMPLATE another_database_name`	Create a database with another database structure and data within the same server
`DROP DATABASE IF EXISTS database_name`	Delete a database
`ALTER DATABASE database_name RENAME TO database_new_name`	Rename a database
`VACUUM FULL VERBOSE ANALYZE`	Optimize database
`show all`	Display configuration parameters
`pg_dump database \\| gzip -9 > database.bak.sql.gz`	Database Backup
`REASSIGN OWNED BY one_user TO another_user`	Change the ownership of database objects owned by a database role (works at database level once inside it \c database)
`DROP OWNED BY one_user`	Can safely come after the `REASSIGN OWNED BY` command

Table

Command	Description
`\dt+`	Show all tables
`DROP TABLE IF EXISTS table_name`	Delete a table
`\copy (SELECT * FROM table_name) TO 'file_path_and_name.csv' WITH CSV`	Export a table as CSV

User

Command	Description
`\du+`	List all users
`CREATE USER user_name WITH PASSWORD '****'`	Create a user
`DROP USER user_name`	Delete an user
`ALTER ROLE user_name SET client_encoding TO 'utf8'`	Change encoding for an user
`ALTER ROLE user_name SET timezone TO 'UTC'`	Change timezone for an user
`ALTER ROLE user_name WITH PASSWORD '****'`	Change a user password
`ALTER ROLE name RENAME TO new_name`	Change a user name
`GRANT ALL PRIVILEGES ON DATABASE database_name TO user_name`	Extend user privileges
`REVOKE ALL PRIVILEGES ON DATABASE database_name FROM user_name`	Remove privileges from user
`COMMENT ON ROLE role_name IS 'description text'`	Add description to `role`, `table`, `database`…
`SELECT usename, useconfig FROM pg_shadow`	View users parameters
`GRANT pg_read_all_stats TO user_name`	Add access privileges to an user (1)
`REVOKE pg_read_all_stats FROM user_name`	Remove access privileges

(1) PostgreSQL provides a set of default roles which provide access to certain, commonly needed, privileged capabilities and information

How To Delete Duplicate Rows in PostgreSQL

python manage.py dbshell
--
-- Views duplicate rows
--
SELECT title, COUNT(DISTINCT(id)), LEFT(abstract, 50) AS abstract
FROM publications GROUP BY title, abstract
HAVING COUNT(DISTINCT(id)) > 1 ORDER BY title, abstract DESC ;
SELECT title FROM publications WHERE id IN (
    SELECT id FROM (SELECT id, ROW_NUMBER() OVER (
        PARTITION BY title, abstract ORDER BY abstract DESC
    ) AS row_num FROM publications
) t WHERE t.row_num > 1) ;
--
-- Deleting duplicate rows using subquery
--
DELETE FROM publications WHERE id IN (
    SELECT id FROM (SELECT id, ROW_NUMBER() OVER (
        PARTITION BY title, abstract ORDER BY abstract DESC
    ) AS row_num FROM publications
) t WHERE t.row_num > 1) ;
\q
  

src: How To Delete Duplicate Rows in PostgreSQL.

Show active sessions on a database

List active sesssions on databases

SELECT pid AS process_id,
       usename AS username,
       datname AS database_name,
       client_addr AS client_address,
       application_name,
       backend_start,
       state,
       state_change
FROM pg_stat_activity ;
  

Kill idle ones:

SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname IN ('database1', 'database2') ;
  

src: Show active sessions on a database

Replicate Databases over Servers with `pg_dump` command

CREATE DATABASE database_name ;
GRANT ALL PRIVILEGES ON DATABASE database_name TO user_name ;
  

PGPASSWORD="****" pg_dump --clean --no-owner --no-privileges --verbose -C \
-h myPgServer.postgres.database.azure.com -U user_name database_name |\
PGPASSWORD="***" psql -h myOtherPgServer.postgres.database.azure.com -U user_name database_name
  

ALTER DATABASE database_name RENAME TO database_new_name ;
  

src: pg_dump extract a PostgreSQL database into a script file or other archive file.

src: How to copy a database to another server.

Change Table & Sequence ownership

-- ALTER TABLE
SELECT 'ALTER TABLE '|| schemaname || '."' || tablename ||'" OWNER TO <NEW_OWNER_ROLE> ;'
FROM pg_tables WHERE NOT schemaname IN ('pg_catalog', 'information_schema')
ORDER BY schemaname, tablename;
-- ALTER SEQUENCE
SELECT 'ALTER SEQUENCE '|| sequence_schema || '."' || sequence_name ||'" OWNER TO <NEW_OWNER_ROLE> ;'
FROM information_schema.sequences WHERE NOT sequence_schema IN ('pg_catalog', 'information_schema')
ORDER BY sequence_schema, sequence_name;
-- REASSIGN OWNED
\c database_name ;
REASSIGN OWNED BY one_user TO another_user ;
  

CSV & JSON Gzip Data Dump

Optionally with Django

python manage.py dbshell

-- EXPORT QUERY TO GZIP CSV DUMP
\copy (SELECT id, title, ... FROM some_table WHERE id NOT LIKE 'CRITERIA-%' ORDER BY id)
TO PROGRAM 'gzip > ~/path-to-a-dump-data-folder/dump_`date +"%Y%m%d_%H%M%S"`.csv.gz'
WITH (FORMAT CSV, HEADER, FORCE_QUOTE(title)) ;
  

src: Export to CSV and Compress with GZIP in Postgres.

-- EXPORT QUERY GZIP JSON DUMP
\copy (SELECT JSON_AGG(ROW_TO_JSON(T)) :: text FROM
(SELECT id, title, ... FROM some_table WHERE id NOT LIKE 'CRITERIA-%' ORDER BY id) AS T)
TO PROGRAM 'gzip > ~/path-to-a-dump-data-folder/dump_`date +"%Y%m%d_%H%M%S"`.json.gz' ;
  

src: How to export PostgreSQL data to a JSON file.

Timezone

Diagnosis

SHOW timezone ;
SELECT * FROM pg_timezone_names ;
  

References 📚

PostgreSQL 14.4 Documentation (postgresql.org).
PostgreSQL interactive terminal psql (postgresql.org).
Django PostgreSQL notes (docs.djangoproject.com).
Postgres Cheat Sheet.

Sponsorship

If this project helps you, you can offer me a cup of coffee ☕️ :-)