postgresql-psql

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

# Connect with username and host
psql -U username -h hostname -p 5432 -d database_name

# Connect using connection string
psql postgresql://username:password@hostname:5432/database_name

# Connect with password prompt
psql -U postgres -h localhost -W

# Connect to specific database on local machine
psql -d myapp_development

# Environment variables (alternative)
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

# Require SSL
psql -h hostname -sslmode require -U username database

# Verify certificate
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

# SSL modes: disable, allow, prefer (default), require, verify-ca, verify-full

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

---

*Content truncated.*