pgcli — PostgreSQL CLI with Auto-Completion and Syntax Highlighting

What it is

pgcli is a Python-based interactive command-line interface for PostgreSQL that serves as a modern replacement for psql. It adds intelligent auto-completion for table names, column names, SQL keywords, and functions, plus syntax highlighting, multi-line editing, and formatted output.

The tool targets database administrators, backend developers, and data engineers who work with PostgreSQL from the terminal and want a more productive CLI experience.

How it saves time or tokens

pgcli eliminates the constant context-switching of checking table schemas before writing queries. Auto-completion surfaces column names as you type, reducing typos and the need to run \d table_name repeatedly. Syntax highlighting catches errors visually before execution.

How to use

1. Install pgcli: pip install pgcli or brew install pgcli.
2. Connect to your database: pgcli -h localhost -U postgres -d mydb.
3. Start typing SQL -- auto-completion and highlighting activate automatically.

Example

# Install
pip install pgcli

# Connect to a local database
pgcli -h localhost -U postgres -d mydb

# Inside pgcli, auto-completion works as you type:
# SELECT u  -> suggests: users, updates, uuid_generate_v4()
# SELECT * FROM users WHERE  -> suggests column names: id, name, email

# Useful pgcli-specific commands:
# \f   - toggle formatted output
# \x   - expanded display
# \dt  - list tables
# F2   - toggle multi-line mode

Related on TokRepo

• AI Tools for Database -- database tools and utilities for developers
• Automation Tools -- tools that speed up repetitive development tasks

Common pitfalls

• pgcli's auto-completion queries the database schema on startup. For databases with thousands of tables, this can cause a slow initial connection. Use --no-auto-complete for large schemas.
• pgcli stores command history in ~/.config/pgcli/history. Be careful with sensitive queries in shared environments.
• Some psql backslash commands behave slightly differently in pgcli. Check \? inside pgcli for the exact supported set.