psql and Client Tools

Learning Focus

Use this lesson to become productive with psql and understand when to use GUI alternatives. psql is the most important PostgreSQL tool — every serious DBA and developer uses it daily.

psql Overview

The Primary CLI Tool for PostgreSQL:

• Official interactive terminal from the PostgreSQL project
• Capabilities:
  • SQL execution with full transaction support
  • Meta-commands for inspecting database objects
  • Scripting and automation (-f, -c, \i)
  • Output formatting (aligned, CSV, HTML, JSON-like)
  • Tab completion for SQL keywords, table names, and columns

Key Features:

• Syntax highlighting (with readline support)
• Query timing (\timing)
• Expanded output for wide rows (\x)
• History search (Ctrl+R like bash)
• Pager support for long output
• Environment variable integration (PGHOST, PGUSER, PGDATABASE)

Connecting with psql

Basic Connection

# Connect to local instance (peer authentication on Linux)
sudo -u postgres psql

# Connect with explicit parameters
psql -h localhost -U postgres -d pg_lab

# Connect with connection string (URI)
psql "postgresql://postgres:password@localhost:5432/pg_lab"

Connection Parameters

Parameter	Flag	Environment Variable	Default
Host	-h	PGHOST	Local socket
Port	-p	PGPORT	5432
User	-U	PGUSER	OS username
Database	-d	PGDATABASE	Username
Password	(prompted)	PGPASSWORD	—

Password File (.pgpass)

Avoid typing passwords repeatedly:

# ~/.pgpass format: hostname:port:database:username:password
echo "localhost:5432:pg_lab:postgres:postgres" >> ~/.pgpass
chmod 600 ~/.pgpass

Essential Meta-Commands

Database Navigation

\l              List all databases (with sizes and owners)
\l+             List databases with extended info
\c DATABASE     Connect to a different database
\conninfo       Show current connection info

Schema Inspection

\dn             List schemas
\dt             List tables in current schema
\dt schema.*    List tables in a specific schema
\dt+            List tables with sizes
\d TABLE        Describe table structure
\d+ TABLE       Describe table with storage and comments
\di             List indexes
\dv             List views
\dm             List materialized views
\df             List functions
\du             List roles/users
\dp             List table permissions (ACLs)

Query Execution

\timing         Toggle query execution timing
\x              Toggle expanded (vertical) output
\a              Toggle aligned/unaligned output
\pset format    Set output format (aligned, csv, html, latex)
\g              Execute the current query buffer
\g filename     Execute and write output to file
\o filename     Redirect all output to file
\o              Stop redirecting output

Utility Commands

\i filename     Execute SQL from a file
\e              Open query in external editor ($EDITOR)
\s              Show command history
\s filename     Save history to file
\q              Quit psql
\?              Show all meta-commands
\h COMMAND      Show SQL command help (e.g., \h CREATE TABLE)
\! command      Execute a shell command

Practical psql Workflows

Workflow 1: Exploring a New Database

-- 1. Connect and orient yourself
\c pg_lab
\dn                         -- what schemas exist?
\dt                         -- what tables are in public?
\d+ customers               -- inspect table structure

-- 2. Sample data
SELECT * FROM customers LIMIT 5;

-- 3. Check row counts
SELECT count(*) FROM customers;
SELECT count(*) FROM orders;

-- 4. Inspect relationships
\d+ orders                  -- look for foreign key constraints

Workflow 2: Running a Script File

# Execute a SQL file
psql -h localhost -U postgres -d pg_lab -f setup.sql

# Execute a single command
psql -h localhost -U postgres -d pg_lab -c "SELECT count(*) FROM users;"

# Execute and output as CSV
psql -h localhost -U postgres -d pg_lab \
  -c "SELECT * FROM users" \
  --csv -o users_export.csv

Workflow 3: Using Variables

-- Set a psql variable
\set table_name 'customers'

-- Use it in a query
SELECT count(*) FROM :table_name;

-- Prompt for input
\prompt 'Enter table name: ' table_name
SELECT * FROM :table_name LIMIT 5;

Workflow 4: Transaction Control

BEGIN;
INSERT INTO users (name) VALUES ('Alice');
INSERT INTO users (name) VALUES ('Bob');
-- Check before committing
SELECT * FROM users ORDER BY id DESC LIMIT 5;
COMMIT;
-- Or ROLLBACK; if something went wrong

psql Configuration File (.psqlrc)

Customize your psql environment:

# ~/.psqlrc
\set HISTSIZE 5000
\set HISTCONTROL ignoredups
\set COMP_KEYWORD_CASE upper
\set PROMPT1 '%[%033[1;32m%]%n@%/%R%#%[%033[0m%] '
\pset null '∅'
\timing
\x auto

Setting	Effect
HISTSIZE	Number of commands to keep in history
HISTCONTROL	Skip duplicate entries
COMP_KEYWORD_CASE	Autocomplete keywords in UPPER CASE
PROMPT1	Custom colored prompt showing user@database
null	Display NULL values as ∅ instead of blank
\timing	Always show query execution time
\x auto	Automatically switch to expanded output for wide results

Alternative Client Tools

a. pgAdmin 4 (Official PostgreSQL GUI)

• Official web-based administration tool

• Features:

  • Visual query builder
  • Server dashboard with live statistics
  • Object browser (databases, schemas, tables, indexes)
  • User/role management
  • Backup/restore wizards
  • ERD diagram tool (pgAdmin 4.28+)

• Installation:

  # Ubuntu/Debian
  sudo apt install pgadmin4-desktop
  
  # macOS
  brew install --cask pgadmin4

b. DBeaver (Universal Database Tool)

• Open-source, multi-platform GUI

• Supports PostgreSQL, MySQL, SQLite, Oracle, and many others

• Features:

  • SQL editor with autocomplete
  • ER diagrams
  • Data export/import (CSV, JSON, SQL, XML)
  • SSH tunnel support

• Installation:

  # Ubuntu/Debian
  sudo snap install dbeaver-ce
  
  # macOS
  brew install --cask dbeaver-community

c. DataGrip (JetBrains IDE)

• Commercial IDE (free for students)
• Features:
  • Intelligent SQL completion
  • Diff and merge for schemas
  • Version control integration
  • Refactoring support
• Best for: developers who use other JetBrains tools

d. TablePlus

• Modern native app (macOS/Windows/Linux)
• Key features:
  • Clean, fast interface
  • Inline editing
  • Multiple tabs and connections
  • SSH tunnel support

e. Adminer

• Lightweight PHP-based tool (single file)
• Quick deployment for web-based access

Tool Comparison

Feature	psql	pgAdmin	DBeaver	DataGrip
Platform	All (CLI)	All (Web/Desktop)	All (Desktop)	All (Desktop)
Visual Modeling	❌	✅ (ERD)	✅ (ERD)	✅ (ERD)
Scripting/Automation	✅ Best	❌ Limited	❌ Limited	✅ Good
Multi-DB Support	❌ PostgreSQL only	❌ PostgreSQL only	✅ Many databases	✅ Many databases
Query Autocomplete	✅ Basic	✅ Good	✅ Good	✅ Excellent
Performance Dashboard	❌	✅ Built-in	❌	❌
Free	✅	✅	✅ (Community)	❌ (Paid)
Best For	Scripting, debugging, DBA	Administration, monitoring	Multi-DB development	Professional development

Integration with Programming Languages

Python (psycopg)

import psycopg

with psycopg.connect("postgresql://postgres:password@localhost/pg_lab") as conn:
    with conn.cursor() as cur:
        cur.execute("SELECT * FROM users")
        for row in cur:
            print(row)

Node.js (node-postgres)

const { Pool } = require('pg');
const pool = new Pool({
  connectionString: 'postgresql://postgres:password@localhost/pg_lab'
});

const result = await pool.query('SELECT * FROM users');
console.log(result.rows);

VS Code Extension

• PostgreSQL by cweijan
• Features:
  • Query execution within VS Code
  • Schema browsing in sidebar
  • Auto-completion
  • Result export

Security Best Practices

1. Use .pgpass or environment variables instead of passwords on the command line

2. Always use SSL for remote connections (sslmode=require)

3. Limit the postgres superuser to local connections only

4. Use SSH tunneling for remote access:

   ssh -L 5432:localhost:5432 user@remote-server
   psql -h localhost -U postgres -d pg_lab

FAQ

Q: Can I use pgAdmin with cloud databases?

A: Yes — configure connection to AWS RDS, Google Cloud SQL, Azure Database, or any accessible PostgreSQL instance.

Q: How do I export query results to CSV?

A: Use psql: \copy (SELECT * FROM users) TO 'users.csv' CSV HEADER

Q: Can I edit table data directly in a GUI?

A: Yes — pgAdmin, DBeaver, and DataGrip all support inline data editing.

Q: What's the difference between \copy and COPY?

A: \copy runs on the client side (your machine). COPY runs on the server side (needs server file access). For development, use \copy.

Learning Resources

1. PostgreSQL psql Documentation: https://www.postgresql.org/docs/current/app-psql.html
2. pgAdmin Documentation: https://www.pgadmin.org/docs/
3. Built-in help: \? (meta-commands) and \h (SQL syntax)

Common Pitfalls

Pitfall	Consequence	Prevention
Forgetting semicolons in psql	Query buffer keeps growing, nothing executes	Always end SQL statements with ;
Using PGPASSWORD in scripts	Password visible in process list	Use .pgpass file or PGPASSFILE env var
Not using \timing	No awareness of query performance	Add \timing to .psqlrc
Quitting without COMMIT	Uncommitted changes are lost (rolled back)	Always explicitly COMMIT or ROLLBACK

Quick Reference

# Connect to PostgreSQL
psql -h localhost -U postgres -d pg_lab

# Run a SQL file
psql -h localhost -U postgres -d pg_lab -f script.sql

# Execute a single command
psql -c "SELECT version();"

# Export to CSV
psql -c "SELECT * FROM users" --csv -o output.csv

# Essential psql meta-commands
\l        list databases
\c NAME   connect to database
\dn       list schemas
\dt       list tables
\d+ T     describe table T in detail
\di       list indexes
\x        toggle expanded output
\timing   toggle query timing
\q        quit

What's Next

• Previous: Installation — Review the previous lesson to reinforce context.
• Next: Initial Server Configuration — Continue to the next concept with incremental complexity.
• Section Overview — Return to this section index and choose another related lesson.