Skip to content
⌘cmdbook.dev
Po

PostgreSQL

psql workflows, schema changes, analytical queries, and admin tasks.

11 commandsdatabasesqlpsql
← OverviewJump to commands

Command catalog

Connection toolkitPostgreSQL

psql

Connect to PostgreSQL databases using the interactive terminal.

psql -U <user> -d <database>
Connection toolkitPostgreSQL

psql \conninfo

\l

View current connection details or list available databases.

\conninfo
Schema evolutionPostgreSQL

CREATE DATABASE

CREATE SCHEMA

Provision databases and namespaces for isolation.

CREATE DATABASE <db_name>;
Schema evolutionPostgreSQL

CREATE EXTENSION

Enable extensions such as UUID generation or citext.

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
Schema evolutionPostgreSQL

ALTER TABLE

Add columns, constraints, or indexes to existing tables.

ALTER TABLE <table> ADD COLUMN <column> TEXT;
Data manipulationPostgreSQL

SELECT

Retrieve rows with advanced filtering and window functions.

SELECT * FROM <table> WHERE status = 'active' ORDER BY created_at DESC LIMIT 50;
Data manipulationPostgreSQL

COPY

Bulk import or export table data using CSV or text formats.

\copy <table> FROM '<path>.csv' CSV HEADER;
Data manipulationPostgreSQL

INSERT

UPDATEDELETE

Modify rows within transactional blocks.

INSERT INTO <table> (<column1>,<column2>) VALUES ('<value1>','<value2>') RETURNING id;
Admin and tuningPostgreSQL

pg_dump

pg_restore

Create logical backups and restore them to target instances.

pg_dump -Fc -f backup.dump -d <database>
Admin and tuningPostgreSQL

VACUUM

ANALYZE

Maintain table statistics and reclaim storage.

VACUUM (VERBOSE, ANALYZE) <table>;
Admin and tuningPostgreSQL

ALTER ROLE

GRANT

Manage access control for users and roles.

ALTER ROLE <role> WITH LOGIN PASSWORD '<password>';