einverne/dotfiles
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
FAQ
Use a ~/.pgpass file with hostname:port:database:username:password and set file permissions to 600, or export PGPASSWORD in controlled environments.
What’s the difference between COPY and \copy?
COPY runs on the server and requires server filesystem access; \copy runs client-side and reads/writes files from the psql client machine, often requiring fewer privileges.
19 skills
This skill guides you through using PostgreSQL psql for interactive queries, scripts, connections, and administration to streamline database tasks.
This skill helps you write effective tests, apply TDD, and improve coverage across Python and JavaScript projects for higher quality.
This skill helps you implement accessible shadcn/ui components in React apps using Tailwind and Radix, speeding UI development and design system integration.
This skill helps you generate and edit high-quality images from text prompts using Google's Gemini 2.5 model, enabling iterative visual refinement.
This skill helps you develop and deploy Cloudflare Workers by guiding bindings, runtimes, wrangler usage, and production workflows.
This skill helps you identify bottlenecks, profile performance, and implement targeted optimizations to improve efficiency across code and systems.
This skill helps you implement RemixIcon icons across web, React, and Vue projects with line and fill variants for consistent UI.
This skill helps you extract structured data, summarize, and answer questions from PDFs using Google Gemini's vision capabilities.
This skill helps you implement and optimize Turborepo monorepos, enabling efficient task pipelines, caching, and cross-package orchestration.
This skill helps you automate headless browser tasks with Cloudflare Browser Rendering for screenshots, PDFs, data extraction, and testing.
This skill guides installing, authenticating, configuring, and automating tasks with the Google Cloud SDK to streamline cloud operations.
This skill performs systematic code reviews to identify quality, security, and performance issues and provide actionable improvement recommendations.
This skill helps you master FFmpeg for media encoding, conversion, and filtering, enabling fast, quality-focused processing of video and audio.
This skill analyzes audio with transcription, summarization, and understanding, and generates natural speech using Google's Gemini API.
This skill helps you systematically identify and resolve bugs in code, configs, and systems by guiding structured debugging workflows.
This skill helps you implement framework-agnostic authentication with TypeScript by guiding setup, session management, and plugin-enabled features.
This skill analyzes videos with Google's Gemini API to summarize, answer questions, transcribe with visuals, and extract timestamped insights.
This skill packages entire repositories into AI friendly formats, enabling efficient context for AI analysis, security audits, and documentation.
This skill guides building Shopify apps, extensions, and themes using GraphQL/REST APIs, CLI, and Polaris for seamless store integrations.
