PostgreSQL Banner with Logo, Elephant and Slogan

PostgreSQL 🐘 Cheatsheet

PostgreSQL 15.2 License EUPL 1.2 Become a sponsor to JV-conseil Follow JV conseil on StackOverflow Follow JVconseil on Twitter Follow JVconseil on Mastodon Follow JV conseil on GitHub

Table of Content

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 title, author, year FROM books WHERE category NOT LIKE 'fiction'
 ) AS T
) TO PROGRAM
'sed ''s~\\\\~\\~g'' | gzip > ./books-`date +"%Y%m%d_%H%M%S"`.json.gz' ;

src: How to export PostgreSQL data to a JSON file • Postgres row_to_json produces invalid JSON with double escaped quotes

Timezone

Diagnosis

SHOW timezone ;
SELECT * FROM pg_timezone_names ;

References 📚

Sponsorship

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

Become a sponsor to JV-conseil