alpha

PostgreSQL

Quick reference for PostgreSQL commands and patterns

#postgres#database#sql

Connecting to PostgreSQL from Command Line

# Connect to local database
psql -U postgres -d database

# Connect with connection string
psql "postgresql://user:password@localhost:5432/database"

# Connect to remote host
psql -h hostname -U user -d database

Common environment variables:

PGHOST=localhost
PGPORT=5432
PGUSER=postgres
PGPASSWORD=secret
PGDATABASE=database

Common psql Commands for Database Exploration

-- List databases
\l

-- Connect to database
\c database

-- List tables
\dt

-- Describe table structure
\d table

-- List indexes
\di

-- Show table size
\dt+ table

Using PostgreSQL with Python and SQLAlchemy

uv pip install psycopg2-binary sqlalchemy
from sqlalchemy import create_engine, text

engine = create_engine("postgresql://user:password@localhost:5432/database")

with engine.connect() as conn:
    result = conn.execute(text("SELECT * FROM users LIMIT 10"))
    for row in result:
        print(row)

Pandas requires SQLAlchemy for read_sql operations:

import pandas as pd

df = pd.read_sql("SELECT * FROM users", engine)

References