postgres command cheatsheet

pg_cheat_sheet.md

Simple cheatsheet

(thanks mr. apollo): https://gist.github.com/apolloclark/ea5466d5929e63043dcf

Query to fetch all constraints on a table:

SELECT
    con.conname AS constraint_name,
    con.contype AS constraint_type,
    pg_get_constraintdef(con.oid) AS constraint_definition
FROM
    pg_catalog.pg_constraint con
JOIN
    pg_catalog.pg_class rel ON rel.oid = con.conrelid
JOIN
    pg_catalog.pg_namespace nsp ON nsp.oid = con.connamespace
WHERE
    nsp.nspname = 'public' 
    AND rel.relname = '<table_name>'; -- Replace with your table name

Query to fetch indexes on a table:

SELECT
    indexname,
    indexdef
FROM
    pg_indexes
WHERE
    tablename = '<table_name>';

Query to fetch size of all tables in a database:

SELECT
    relname AS table_name,
    pg_size_pretty(pg_total_relation_size(oid)) AS total_size
FROM
    pg_class
WHERE
    relkind = 'r' -- 'r' for regular tables
    AND relnamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'public') -- Adjust 'public' to your schema if different
ORDER BY
    pg_total_relation_size(oid) DESC;

Query sequence to delete duplicates from a table which has no primary key safety:

(using ctid)

BEGIN;

DELETE FROM organization
WHERE ctid NOT IN (
    SELECT MIN(ctid)
    FROM organization
    GROUP BY id  -- your PK or business key
);

-- Verify the count looks right before committing
SELECT COUNT(*) FROM organization;

ROLLBACK; -- change to COMMIT when you're confident