pgcli Enhanced Postgres

pgcli cheatsheet — PostgreSQL client with autocomplete, syntax highlighting, history. pgcli -U user dbname, \dt tables, \d tablename. Better than psql shell.

6 min read

What it is

pgcli is an enhanced command-line interface for PostgreSQL that provides autocompletion, syntax highlighting, and an intuitive user experience.

Installation

Linux:

sudo apt update && sudo apt install pgcli # Debian/Ubuntu
sudo dnf install pgcli # Fedora
sudo pacman -S pgcli # Arch Linux

macOS:

brew install pgcli

Windows: pgcli is typically installed via pip. Ensure you have Python and pip installed.

pip install pgcli

If you encounter issues with SSL certificates on Windows, you might need to install pyOpenSSL:

pip install pyopenssl

Commands / Usage

Connecting to a Database

Connect to a PostgreSQL server.

pgcli postgresql://user:password@host:port/database
  • Connects to the specified database with username, password, host, and port.
pgcli -h localhost -p 5432 -U myuser -d mydb
  • Connects to mydb on localhost:5432 as user myuser.
pgcli
  • Prompts for connection details (host, port, user, database).

Running SQL Queries

Execute a single SQL statement and exit.

pgcli -c "SELECT 1;" postgresql://user:password@host:port/database
  • Runs the SQL command SELECT 1; and then exits pgcli.

Execute multiple SQL statements from a file.

pgcli -f my_script.sql postgresql://user:password@host:port/database
  • Executes all SQL commands found in my_script.sql.

Execute SQL commands interactively.

pgcli postgresql://user:password@host:port/database
  • Enters the interactive pgcli shell.

Query Execution and Output

Execute a query and display results in a table.

SELECT * FROM users LIMIT 5;
  • Fetches and displays the first 5 rows from the users table.

Execute a query and display results in CSV format.

\fmt csv
SELECT id, name FROM products;
  • Sets the output format to CSV and then selects id and name from products.

Execute a query and display results in JSON format.

\fmt json
SELECT json_agg(row_to_json(products)) FROM products WHERE price > 100;
  • Sets the output format to JSON and aggregates product data into a JSON array.

Execute a query and display results in HTML format.

\fmt html
SELECT * FROM orders WHERE status = 'shipped';
  • Sets the output format to HTML and fetches all columns for shipped orders.

Execute a query and display results in Unaligned format (no separators).

\fmt unaligned
SELECT COUNT(*) FROM logs;
  • Sets the output format to unaligned and counts the number of log entries.

Database Navigation and Information

List available databases.

\l
  • Lists all databases on the connected server.

List tables in the current database.

\dt
  • Displays a list of tables in the current database.

List tables with schema information.

\dt+
  • Displays tables with their sizes, descriptions, and other details.

List all schemas.

\dn
  • Lists all schemas in the current database.

List all functions.

\df
  • Lists all available functions in the current database.

Describe a table’s structure.

\d users
  • Shows the columns, data types, and constraints of the users table.

Describe a table’s structure with extended information.

\d+ users
  • Shows detailed information about the users table, including indexes and storage parameters.

Show the current database and user.

\conninfo
  • Displays connection information such as the current database, user, and host.

Show PostgreSQL version.

SELECT version();
  • Executes a SQL query to retrieve the PostgreSQL server version.

Editing and History

Edit the current SQL command in your default editor.

\e
  • Opens the current command buffer in your editor. Save and exit to execute.

Edit a specific command from history.

\e 5
  • Opens the 5th command from your history in your editor.

View command history.

\history
  • Displays a list of previously executed commands.

Clear command history.

\clear
  • Clears the current session’s command history.

Shell Features

Run shell commands.

\! ls -l
  • Executes the ls -l command in the system shell.

Exit pgcli.

\q
  • Exits the pgcli session.

Quit pgcli without saving history.

\quit
  • Exits pgcli immediately, discarding current session history.

Show help for pgcli commands.

\?
  • Displays help information for pgcli meta-commands.

Show help for SQL syntax.

\h SELECT
  • Displays help information for the SELECT SQL statement.

Autocompletion and Highlighting

Tab completion for SQL keywords, table names, column names, and functions. (This is an automatic feature, no specific command needed. Just start typing and press Tab.)

Syntax highlighting for SQL queries. (This is an automatic feature, no specific command needed. Queries are highlighted as you type.)

Configuration

Edit pgcli configuration file.

pgcli --config-dir
  • Prints the directory where pgcli stores its configuration. You can edit the config file within this directory.

Set connection string in environment variable.

export PGCLI_DSN="postgresql://user:password@host:port/database"
pgcli
  • Connects using the DSN defined in the PGCLI_DSN environment variable.

Common Patterns

Connect to a database and run a script:

pgcli -f my_data_load.sql postgresql://admin:secure@db.example.com:5432/production
  • Executes a SQL script to load data into the production database.

Explore table structure and then query data:

pgcli mydatabase
\d users
SELECT COUNT(*) FROM users WHERE active = TRUE;
  • Connects to mydatabase, inspects the users table schema, and then counts active users.

Fetch data and save to a CSV file:

pgcli -c "\fmt csv; SELECT * FROM customers WHERE country = 'USA';" postgresql://user:pass@host/db > us_customers.csv
  • Connects, sets output to CSV, queries US customers, and redirects the output to a file.

Run a query, edit it, and re-run:

pgcli mydatabase
SELECT * FROM orders WHERE order_date > '2023-01-01';
\e
-- Make edits in the editor, save and exit
  • Executes an initial query, then uses \e to modify and re-execute it.

Quickly check a table’s content in different formats:

pgcli mydatabase
\dt  # List tables
\d+ orders # Describe orders table
\fmt json
SELECT order_id, total_amount FROM orders LIMIT 10;
\fmt table # Switch back to default table format
  • A workflow to quickly inspect table names, schema, and sample data in JSON format.

Gotchas

Password Prompting: If you connect without providing a password in the connection string and your pg_hba.conf requires it, pgcli will prompt you interactively. It does not store passwords.

SSL Certificate Verification: On some systems, especially Windows, pgcli might fail to connect due to SSL certificate issues. You might need to ensure pyOpenSSL is installed (pip install pyopenssl) or configure your PostgreSQL server and client for proper SSL.

Large Query Results: For very large result sets, displaying them directly in the terminal might be slow or overwhelming. Use \fmt csv, \fmt json, or redirect output to a file (> filename.ext) for better handling.

Meta-commands vs. SQL: Remember that commands starting with \ (like \dt, \d, \l) are pgcli meta-commands, not SQL. They are processed by pgcli itself for database introspection and session control. Standard SQL commands are executed directly against the database.

Connection String Format: Ensure your connection string adheres to the standard PostgreSQL URI format: postgresql://[user[:password]@][netloc][:port][/dbname][?param1=value1&...]. Missing components might lead to connection errors or unexpected prompts.

Editor Configuration: The \e command uses the editor specified by the EDITOR or VISUAL environment variables. If these are not set, it defaults to vi or vim. Set export EDITOR=nano (or your preferred editor) in your shell if you want a different default.