# pgcli — PostgreSQL CLI with Auto-Completion and Syntax Highlighting

> pgcli is a modern `psql` replacement in Python with intelligent auto-completion for tables/columns/functions, syntax highlighting, multi-line editing, and output formatting.

## Install

Save in your project root:

# pgcli — PostgreSQL CLI with Auto-Completion and Syntax Highlighting

## Quick Use
```bash
brew install pgcli
pgcli postgres://user:pass@host:5432/dbname
# Inside pgcli
> dt                 -- list tables
> d users            -- describe users table
> SELECT email FROM   -- triggers column auto-complete
> i query.sql        -- run file
> x                  -- toggle expanded output
```

## 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

---
Source: https://tokrepo.com/en/workflows/44395a8b-38c4-11f1-9bc6-00163e2b0d79
Author: AI Open Source