pgcli

pgcli is an interactive command-line client for PostgreSQL databases that provides enhanced features beyond the standard psql client. It offers auto-completion for SQL keywords, table names, and column names, along with syntax highlighting using Pygments to make query writing more efficient and readable.

The tool includes smart context-sensitive completion that suggests relevant options based on the current query context. For example, typing "SELECT * FROM " will only show table names, while "SELECT * FROM users WHERE " will display column names from the users table. It supports standard PostgreSQL connection methods including connection strings, environment variables (PGHOST, PGPORT, etc.), and SSL configurations.

Built on Python Prompt Toolkit, pgcli supports many psql back-slash commands and pretty-prints tabular data results. It can be integrated into IPython workflows through the pgcli.magic extension, allowing users to drop into a pgcli session from within IPython, execute queries, and return results to the Python workspace. The tool maintains a configuration file at ~/.config/pgcli/config for customization and supports various output modes including automatic vertical formatting for wide results.

Installation

# via Homebrew
brew install pgcli

# via APT
sudo apt-get install pgcli

# via pip
pip install -U pgcli