pgcli — PostgreSQL CLI with Auto-Completion and Syntax Highlighting

Introduction

Default psql is rock-solid but its readline experience is stuck in 1996: no column auto-completion, no syntax highlighting, no pretty output. pgcli gives you IDE-like ergonomics in a REPL, reading the live schema to suggest table/column names, and showing SQL keywords in color while you type.

What pgcli Does

• Connects to PostgreSQL, Redshift, Citus, CrateDB, CockroachDB.
• Context-aware completion: tables after FROM, columns after SELECT.
• Syntax-highlights SQL as you type (Pygments).
• Shows results as pretty tables or expanded records.
• Saved queries via named queries; history with fuzzy search.

Architecture Overview

pgcli wraps psycopg2 for the connection and prompt-toolkit for the REPL. On connect, it introspects information_schema to build a symbol table. Each keystroke the completer filters candidates; Pygments tokenizes the buffer for highlighting.

Self-Hosting & Configuration

• Install via pip, brew, apt, dnf.
• Config at ~/.config/pgcli/config toggles multi-line, auto-vertical, timing.
• [named queries] section stores shortcuts.
• SSH tunneling via --ssh-tunnel user@bastion.
• .pgpass and PG* env vars recognized.

Key Features

• Smart completion — tables, columns, JOIN conditions.
• Inline syntax highlighting.
• x auto switches to expanded view when output is too wide.
• Named queries with parameters.
• Works over SSH bastions.

Comparison with Similar Tools

• psql — official; rock-solid, no completion.
• mycli — same author, MySQL.
• litecli — SQLite equivalent.
• dbeaver — GUI; more features but heavier.
• usql — universal SQL CLI; more DBs, less polished.

FAQ

Q: Safe on production? A: Yes — read-only mode via prompt or simply connect with a read-only role.

Q: Works with PgBouncer? A: Yes; introspection may show only session-mode caches.

Q: Export results? A: copy (SELECT ...) TO out.csv CSV HEADER.

Q: Multi-statement? A: End with ;, press Enter; multi-line edit with Alt+Enter.

Sources

• https://github.com/dbcli/pgcli
• https://www.pgcli.com