query-sql

PostgreSQL SQL Query & Exploration

When to Use This Skill

Use this skill when you need to:

• Explore the schema of the local PostgreSQL database.
• Inspect the structure of specific tables, views, or indexes.
• Draft and execute SQL queries to fetch or analyze data.
• Debug database state or verify data integrity locally.

Prerequisites

• The local PostgreSQL container must be running. Use nx run codebase:postgres-container:up if needed.
• psql (PostgreSQL client) must be installed locally.
• The .env file at the workspace root must contain the default PostgreSQL connection variables (POSTGRES_HOST, POSTGRES_PORT, POSTGRES_USER, POSTGRES_DB, POSTGRES_PASSWORD).

Step-by-Step Workflows

1. Schema Exploration

To explore the database quickly, use psql meta-commands passed via the -c flag. Use set -a; source .env; set +a; to ensure environment variables are automatically exported.

• List all tables:

  set -a; source .env; set +a; PGPASSWORD=$POSTGRES_PASSWORD psql -h ${POSTGRES_HOST:-localhost} -p ${POSTGRES_PORT:-5432} -U ${POSTGRES_USER:-postgres} -d ${POSTGRES_DB:-postgres} -c "\dt"
  
  

• Describe a specific table:

  set -a; source .env; set +a; PGPASSWORD=$POSTGRES_PASSWORD psql -h ${POSTGRES_HOST:-localhost} -p ${POSTGRES_PORT:-5432} -U ${POSTGRES_USER:-postgres} -d ${POSTGRES_DB:-postgres} -c "\d+ table_name"
  
  

• List all schemas:

  set -a; source .env; set +a; PGPASSWORD=$POSTGRES_PASSWORD psql -h ${POSTGRES_HOST:-localhost} -p ${POSTGRES_PORT:-5432} -U ${POSTGRES_USER:-postgres} -d ${POSTGRES_DB:-postgres} -c "\dn"
  
  

2. Query Execution

CRITICAL: NEVER execute multiline or complex queries using the inline -c flag, as this often leads to shell escaping errors. Always use notepads/notepad.sql as a scratchpad.

1. Write the query to the scratchpad: Use your file editing tools to write your SQL query into notepads/notepad.sql.

2. Execute the query: Run the file using the -f flag in the terminal: