postgresql-psql_skill

Overview

This skill is a practical, compact guide to using psql, the PostgreSQL interactive terminal client. It covers connecting, querying, scripting, formatting output, transaction control, and advanced psql meta-commands. The content focuses on real-world workflows for developers and DBAs who manage PostgreSQL from the command line.

How this skill works

psql runs as a REPL that accepts SQL statements and psql-specific backslash commands to inspect and control the session. It maintains a single database connection per session, supports scripting via files and variables, and can format or redirect output to files, CSV, HTML, or pipes. Connection options, authentication methods, and .psqlrc configuration let you customize behavior and automate common tasks.

When to use it

• Connect to a local or remote PostgreSQL instance from the terminal
• Run ad-hoc SQL queries and inspect schema, tables, indexes, and roles
• Automate administrative tasks with SQL scripts and psql meta-commands
• Export/import data, format query results for reporting or pipes
• Manage transactions, savepoints, and perform safe bulk operations

Best practices

• Use connection strings or environment variables (PGUSER, PGHOST, PGDATABASE) to avoid exposing passwords on command lines
• Store credentials securely in ~/.pgpass with correct permissions (chmod 600) for unattended scripts
• Enable timing and \pset options in ~/.psqlrc for consistent, readable output during debugging
• Prefer \copy for client-side import/export when server-side permissions are limited
• Develop and test scripts with --echo-all and --single-transaction flags to catch errors before committing

Example use cases

• Quickly inspect a table structure: start psql and run \d+ my_table to view columns, indexes, and storage info
• Run a migration script in a single transaction: psql -f migrate.sql -v ON_ERROR_STOP=1 --single-transaction
• Export query results to CSV: run \pset format csv then \g output.csv or use \copy (SELECT ...) TO 'file.csv' CSV
• Connect through an SSH tunnel for secure remote access: ssh -L 5432:localhost:5432 user@host then psql -h localhost -U user
• Embed psql in automation: use psql -c "SELECT ..." in CI jobs and capture output with --no-align -t for parsing