$devvkit learn --librarie psql-&-pgcli-guide

psql & pgcli Guide

[postgresql][database][cli][sql]

Database Clients

Install

# psql (part of PostgreSQL):
brew install postgresql@16
sudo apt install postgresql-client
# Windows: download from postgresql.org

# pgcli:
pip install pgcli
brew install pgcli

psql is the official PostgreSQL interactive terminal. The \d commands are indispensable: `\d` lists tables, `\d+ tablename` shows table details, `\l` lists databases, `\du` lists roles. `\x` toggles expanded display for wide rows.

pgcli is a modern PostgreSQL CLI with auto-completion (tables, columns, SQL keywords), syntax highlighting, and smart completion. It uses prompt_toolkit and supports multi-line queries. Ctrl+T opens the help table.

Both tools support .sql file execution: `psql -f schema.sql dbname`. Use -c for one-shot queries. For GUI alternatives: DBeaver, pgAdmin, TablePlus, DataGrip.

psql Basics

Connect to database— Connect with psql.

psql -h localhost -p 5432 -U myuser -d mydb
psql postgresql://myuser:pass@localhost:5432/mydb

# Using pgcli:
pgcli -h localhost -U myuser -d mydb
pgcli postgresql://localhost/mydb

Import/export— Data operations.

# Execute SQL file:
psql -f schema.sql mydb

# Export to CSV:
psql -c "\copy (SELECT * FROM users) TO '/tmp/users.csv' CSV HEADER" mydb

# Import CSV:
psql -c "\copy users FROM '/tmp/import.csv' CSV HEADER" mydb

# Backup/restore:
pg_dump mydb > backup.sql
psql mydb < backup.sql

psql \d Commands

\d commands— Meta-commands for schema.

\l                    # List databases
\c mydb               # Connect to database
\dt                   # List tables
\dt+                  # List tables with sizes
\d table_name         # Describe table
\d+ table_name        # Extended table info
\di                   # List indexes
\dv                   # List views
\df                   # List functions
\du                   # List roles
\dn                   # List schemas

psql Tricks

Query formatting— Make output readable.

\x auto               # Expanded display
\x                    # Toggle expanded
\pset border 2        # Pretty borders
\pset format wrapped  # Wrap long lines
\timing               # Show query timing

Watch query— Repeating queries for monitoring.

# Watch every 2 seconds:
SELECT count(*) FROM active_connections \watch 2

# Via shell:
watch -n 1 "psql -c 'SELECT count(*) FROM users' mydb"

Edit query in vim— External editor.

\e                    # Open last query in $EDITOR
\ef function_name     # Edit function
\set EDITOR vim       # Set editor
# After editing in vim, save+quit: executes query

pgcli

pgcli features— Auto-complete and smart features.

pgcli postgresql://user:pass@localhost:5432/mydb

# Tab completion for tables, columns, SQL keywords
# Syntax highlighting
# Multi-line editing (Ctrl+Enter to execute)
# Ctrl+T: show table help
# Ctrl+R: search history

pgcli config— Customize pgcli.

# ~/.config/pgcli/config
[vim_mode]
enable = true

[main]
syntax_style = monokai
table_format = psql
multi_line_mode = vi