PostgreSQL pgcli
Modern command-line interface for PostgreSQL
🖥️ What is pgcli?
pgcli is a modern command-line interface for PostgreSQL with auto-completion and syntax highlighting. It makes working with PostgreSQL databases easier and more productive for developers.
-- Connect to database using pgcli
pgcli -h localhost -U postgres -d mydb
-- Simple query with auto-completion
SELECT * FROM users;
Output:
+----+----------+------------------+ | id | username | email | +----+----------+------------------+ | 1 | john_doe | [email protected] | | 2 | jane_doe | [email protected] | +----+----------+------------------+
Key Features of pgcli
Auto-completion
Smart suggestions for SQL keywords, table names, and columns as you type
SELECT u. [TAB]
-- Shows: id, username, emailSyntax Highlighting
Color-coded SQL for better readability
SELECT name FROM products
WHERE price > 100;Query History
Access previous queries easily
-- Press UP arrow
-- Navigate through historySmart Output
Formatted tables and export options
\o output.txt
SELECT * FROM orders;🔹 Installation
pgcli is easy to install using pip, the Python package manager. It works on Windows, macOS, and Linux systems.
# Install using pip
pip install pgcli
# Install on macOS using Homebrew
brew install pgcli
# Install on Ubuntu/Debian
sudo apt-get install pgcli🔹 Basic Connection
Connect to PostgreSQL databases using simple commands:
# Connect to local database
pgcli -d database_name
# Connect with username
pgcli -U username -d database_name
# Connect to remote host
pgcli -h hostname -U username -d database_name
# Full connection string
pgcli postgresql://user:password@localhost:5432/mydbOutput:
Server: PostgreSQL 14.5 Version: 3.5.0 Database: mydb postgres@localhost:mydb>
🔹 Common Commands
Essential pgcli commands for database operations:
-- List all databases
\l
-- List all tables
\dt
-- Describe table structure
\d table_name
-- Show table columns
\d+ table_name
-- Execute SQL file
\i script.sql
-- Change database
\c database_name
-- Quit pgcli
\qExample Output for \dt:
+--------+----------+-------+----------+ | Schema | Name | Type | Owner | +--------+----------+-------+----------+ | public | users | table | postgres | | public | products | table | postgres | | public | orders | table | postgres | +--------+----------+-------+----------+
🔹 Query Examples
Write and execute SQL queries with pgcli:
-- Simple SELECT
SELECT * FROM users LIMIT 5;
-- JOIN query
SELECT u.username, o.order_date
FROM users u
JOIN orders o ON u.id = o.user_id;
-- Aggregate function
SELECT COUNT(*) as total_users
FROM users;
-- INSERT data
INSERT INTO users (username, email)
VALUES ('alice', '[email protected]');
-- UPDATE data
UPDATE users
SET email = '[email protected]'
WHERE username = 'alice';🔹 Configuration
Customize pgcli behavior with configuration file:
# ~/.config/pgcli/config
# Location: ~/.config/pgcli/config
[main]
# Enable auto-completion
auto_expand = True
# Multi-line mode
multi_line = True
# Syntax highlighting style
syntax_style = monokai
# Show row count
row_limit = 1000