postgresql.md

Postgresql Cheat Sheet

'SHOW FULL PROCESSLIST;' in postgres

SELECT * FROM pg_stat_activity;

Init auto incremental index

ALTER SEQUENCE check_hrpcs_module_idx_seq RESTART WITH 1;

Export table to CSV file

\COPY tablename TO '<file_path>';
\COPY tablename TO '<file_path>' DELIMITER ';' CSV HEADER;

Import table to CSV file

\COPY tablename from '<file_path>';

Dump

pg_dump -U <USERNAME> --data-only --table=<TABLE> <DB> > <file_name.sql>

Restore

psql -U <USERNAME> <DB> < <file_name.sql>

Query in Shell

psql -U postgres -d <DB> -c "SELECT * FROM <TABLE> WHERE id > -1"