Skip to content
Half Stack Python

Useful postgresql queries

Useful postgresql queries

Section titled “Useful postgresql queries”
SELECT pg_reload_conf();

Copy table as csv to client filesystem

Section titled “Copy table as csv to client filesystem”
\copy table to 'mycsv.csv' DELIMITER ',' CSV HEADER;
\copy (SELECT column1, column2 FROM your_table_name WHERE condition) TO 'query_results.csv' DELIMITER ',' CSV HEADER;

Get table size

Section titled “Get table size”
SELECT pg_size_pretty(pg_total_relation_size('your_table_name'));

Get column size

Section titled “Get column size”
SELECT sum(pg_column_size(column)) FROM yourtable

Transactions

Section titled “Transactions”
begin;
savepoint my_savepoint;


rollback to savepoint my_savepoint;
commit;
rollback;

Add column

Section titled “Add column”
alter table table_name add column column_name data_type [constraint];

Delete

Section titled “Delete”
begin;
DELETE FROM table_name where condition;
-- commit;

Update

Section titled “Update”
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

Deadlocks

Section titled “Deadlocks”
SELECT deadlocks FROM pg_stat_database WHERE datname = current_database();

pg_stat_activity: Shows current connections and their states, including those waiting for locks. pg_locks: Provides details about locks held and requested, including the object being locked (table, row, etc.) and the process ID (PID) involved.