PostgreSQL psql Skill
PostgreSQL psql (PostgreSQL interactive terminal) is the primary command-line client for interacting with PostgreSQL databases. It provides both interactive query execution and powerful scripting capabilities for database management and administration.
When to Use This Skill
Use this skill when:
- Connecting to PostgreSQL databases from the command line
- Executing SQL queries interactively
- Writing SQL scripts for automation
- Creating and managing databases and schemas
- Managing database objects (tables, views, indexes, functions)
- Backing up and restoring databases
- Configuring connections and authentication
- Formatting and exporting query results
- Managing transactions and permissions
- Debugging SQL queries
- Automating database administration tasks
- Setting up replication and high availability
- Creating stored procedures and functions
Core Concepts
REPL Model
- psql operates as an interactive REPL (Read-Eval-Print Loop)
- Accepts SQL commands and meta-commands (backslash commands)
- Maintains connection state across commands within a session
- Supports command history and editing
Command Types
- SQL Commands: Standard SQL statements (SELECT, INSERT, UPDATE, DELETE, etc.)
- Meta-Commands: psql-specific commands prefixed with backslash (e.g.,
\dt, \d)
- Backslash Commands: Control query output, session variables, and psql behavior
Connection Model
- Single database connection per session
- Can switch databases without reconnecting
- Connection state includes current database, user, and search path
- Environmental variables and .pgpass for credential management
Connection Options
Basic Connection Command
psql [OPTIONS] [DBNAME [USERNAME]]
Common Connection Options
psql -U username -h hostname -p 5432 -d database_name
psql postgresql://username:password@hostname:5432/database_name
psql -U postgres -h localhost -W
psql -d myapp_development
export PGUSER=postgres
export PGPASSWORD=mypassword
export PGHOST=localhost
export PGPORT=5432
export PGDATABASE=mydb
psql
Connection String Formats
Standard URI format:
postgresql://[user[:password]@][netloc][:port][/dbname][?param1=value1&...]
Example:
postgresql://app_user:[email protected]:5432/production_db?sslmode=require
Authentication Methods
Password file (.pgpass):
# ~/.pgpass (chmod 600)
hostname:port:database:username:password
localhost:5432:mydb:postgres:mypassword
*.example.com:5432:*:appuser:apppass
Connection via SSH tunnel:
ssh -L 5432:localhost:5432 user@remote-host
psql -U postgres -h localhost
SSL/TLS Connection Options
psql -h hostname -sslmode require -U username database
psql -h hostname -sslmode verify-full \
-sslcert=/path/to/client-cert.crt \
-sslkey=/path/to/client-key.key \
-sslrootcert=/path/to/ca-cert.crt database
Essential Meta-Commands
Database and Schema Navigation
\l or \list # List all databases
\l+ or \list+ # List databases with sizes
\c or \connect DATABASE USER # Connect to different database
\dn or \dn+ # List schemas (namespaces)
\dt or \dt+ # List tables in current schema
\di or \di+ # List indexes
\dv or \dv+ # List views
\dm or \dm+ # List materialized views
\ds or \ds+ # List sequences
\df or \df+ # List functions/procedures
\da or \da+ # List aggregates
\dT or \dT+ # List data types
\dF or \dF+ # List text search configurations
Object Inspection Commands
\d or \d NAME # Describe table, view, index, sequence, or function
\d+ or \d+ NAME # Extended description with details
\da PATTERN # List aggregate functions matching pattern
\db or \db+ # List tablespaces
\dc or \dc+ # List character set encodings
\dC or \dC+ # List type casts
\dd or \dd+ # List object descriptions/comments
\dD or \dD+ # List domains
\de or \de+ # List foreign data wrappers
\dE or \dE+ # List foreign servers
\dF or \dF+ # List text search configurations
\dFd or \dFd+ # List text search dictionaries
\dFp or \dFp+ # List text search parsers
\dFt or \dFt+ # List text search templates
\dg or \dg+ # List database roles/users
\dl or \dl+ # List large objects (same as \lo_list)
\dL or \dL+ # List procedural languages
\dO or \dO+ # List collations
\dp or \dp+ # List table access privileges
\dRp or \dRp+ # List replication origins
\dRs or \dRs+ # List replication subscriptions
\ds or \ds+ # List sequences
\dt or \dt+ # List tables
\dU or \dU+ # List user mapping
\du or \du+ # List roles
\dv or \dv+ # List views
\dx or \dx+ # List extensions
\dX or \dX+ # List extended statistics
Formatting and Output Commands
\a # Toggle between aligned and unaligned output
\C [STRING] # Set table title
\f [STRING] # Set field separator for unaligned output
\H # Toggle HTML output mode
\pset OPTION [VALUE] # Set output option (detailed below)
\t [on|off] # Toggle tuple-only output (no headers/footers)
\T [STRING] # Set HTML table tag attributes
\x or \x [on|off|auto] # Toggle expanded/vertical output
\g or \g [FILENAME|COMMAND] # Execute query and send output to file/command
\pset Options
\pset border [0-2] # Set border display (0=none, 1=ascii, 2=unicode)
\pset columns WIDTH # Set column width limit
\pset csv # Set CSV output format
\pset expanded [on|off|auto] # Toggle expanded output
\pset fieldsep STRING # Set field separator
\pset footer [on|off] # Toggle footer display
\pset format [aligned|unaligned|csv|tsv|html|latex|latex-longtable|troff-ms]
\pset header [on|off] # Toggle header display
\pset linestyle [ascii|old-ascii|unicode] # Set line drawing style
\pset null STRING # Set string to represent NULL
\pset numericlocale [on|off] # Toggle locale-specific number formatting
\pset pager [on|off|always] # Control pager usage
\pset recordsep STRING # Set record separator
\pset recordsep0 [on|off] # Use null terminator between records
\pset tableattr STRING # Set HTML table attributes
\pset title STRING # Set query title
\pset tuples_only [on|off] # Toggle tuple-only mode
File and History Commands
\copy QUERY TO FILENAME [FORMAT] # Client-side COPY (requires fewer permissions)
\copy QUERY TO STDOUT # Copy to standard output
\copy TABLE FROM FILENAME [FORMAT] # Import data from file
\e or \edit # Edit current query buffer in editor
\e FILENAME # Edit file in editor
\ef [FUNCNAME] # Edit function definition
\ev [VIEWNAME] # Edit view definition
\w FILENAME or \write FILENAME # Write current query buffer to file
\i FILENAME or \include FILENAME # Execute SQL commands from file
\ir FILENAME or \include_relative FILE # Execute relative path file
\s [FILENAME] # Show command history (or save to file)
\o FILENAME or \out FILENAME # Send all output to file
\o # Return output to terminal
Batch and Script Commands
\echo TEXT # Print text (useful in scripts)
\errverbose # Show last error in verbose form
\q or \quit # Quit psql
\! COMMAND or \shell COMMAND # Execute shell command
\cd DIRECTORY # Change working directory
\pwd # Print current working directory
\set VARIABLE VALUE # Set psql variable
\unset VARIABLE # Unset psql variable
\setenv VARNAME VALUE # Set environment variable
\getenv VARNAME # Get environment variable value
\prompt [TEXT] VARIABLE # Prompt user for input and set variable
Transaction Commands
\begin or BEGIN # Start transaction
\commit or COMMIT # Commit transaction
\rollback or ROLLBACK # Rollback transaction
\savepoint NAME # Create savepoint
\release SAVEPOINT # Release savepoint
\rollback TO SAVEPOINT # Rollback to savepoint
Information Commands
\d+ TABLENAME # Show table with extended info and storage info
\dt *.* # List all tables in all schemas
\dn * # List all schemas
\du # List all users/roles
\db # List tablespaces
\dx # List installed extensions
\h or \help # List available SQL commands
\h COMMAND or \help COMMAND # Show help for specific SQL command
\? # Show psql help
\copyright # Show PostgreSQL copyright/license info
\version or SELECT version() # Show PostgreSQL version
Command-Line Options
Connection Options
-h, --host=HOSTNAME
-p, --port=PORT
-U, --username=USERNAME
-d, --dbname=DBNAME
-w, --no-password
-W, --password
Output and Formatting Options
-A, --no-align
-c, --command=COMMAND
-C, --copy-only
-d, --dbname=DBNAME
-E, --echo-hidden
-e, --echo-all
-b, --echo-errors
-f, --file=FILENAME
-F, --field-separator=CHAR
-H, --html
-l, --list
-L, --log-file=FILENAME
-n, --no-readline
-o, --output=FILENAME
-P, --pset=VARIABLE=VALUE
-q, --quiet
-R, --record-separator=CHAR
-S, --single-step
-s, --single-transaction
-t, --tuples-only
-T, --table-attr=STRING
-v, --set=VARIABLE=VALUE
-V, --version
-x, --expanded
