Advanced Features Guide¶

-- Save a query with parameters
\ns daily_sales SELECT * FROM orders WHERE created_at >= current_date - interval '$1 days';

-- Execute with parameter
daily_sales 7  -- Shows orders from last 7 days
daily_sales 30 -- Shows orders from last 30 days

-- List saved queries
\n

-- Multiple parameters with $1, $2, etc.
\ns user_orders SELECT * FROM orders WHERE user_id = $1 AND status = '$2' ORDER BY created_at DESC;

-- Use query
user_orders 123 'completed'

-- Parameter expansion with $* (all parameters)
\ns bulk_update UPDATE products SET category = '$1' WHERE id IN ($*);

-- Use with multiple values
bulk_update electronics 1 2 3 4 5

-- Array-style parameters with $@
\ns product_report SELECT * FROM products WHERE category IN ($@);

-- Use with comma-separated values
product_report 'electronics','books','clothing'

-- Only available in current session
\ns session_temp SELECT COUNT(*) FROM temp_analysis_table;

-- Available everywhere
\ns connection_info SELECT current_database(), current_user, version() --global

-- Only available for PostgreSQL connections
\ns pg_stats SELECT schemaname, tablename, n_tup_ins, n_tup_del FROM pg_stat_user_tables --postgres

-- Only available for MySQL connections
\ns mysql_processlist SHOW PROCESSLIST --mysql

-- Only available for SQLite connections
\ns sqlite_tables SELECT name FROM sqlite_master WHERE type='table' --sqlite

\n  -- Shows all queries with scope indicators

-- Output:
-- Named queries:
--   daily_sales      [session]    - SELECT * FROM orders WHERE...
--   connection_info  [global]     - SELECT current_database()...
--   pg_stats         [postgres]   - SELECT schemaname, tablename...

-- Open query in external editor
\ed

-- Edit, save, and close - query executes automatically
-- Empty Enter key executes the last edited query

-- Use \ed with existing query to edit it
SELECT * FROM users WHERE status = 'active';
\ed  -- Opens editor with above query pre-loaded

-- Editor supports multiple statements
BEGIN;
UPDATE users SET last_login = NOW() WHERE id = 123;
INSERT INTO user_activity (user_id, activity) VALUES (123, 'login');
COMMIT;
-- All statements execute as a transaction

-- Force column selection for any query
\cs

-- Now all queries show column selection interface
SELECT * FROM users;  -- Shows column selection even for few columns

-- Toggle off
\cs

-- Set threshold dynamically
\csthreshold 15  -- Changes threshold to 15 columns

-- Clear saved column selections
\clrcs

-- Reset all view settings
\resetview

-- First query on 'users' table - shows selection interface
SELECT * FROM users;
-- Select: id, name, email (deselect others)

-- Later query on same table structure - uses saved selection
SELECT * FROM users WHERE active = true;
-- Automatically shows only: id, name, email

-- Different table structure - new selection
SELECT u.id, u.name, p.title FROM users u JOIN posts p ON u.id = p.author_id;
-- Shows new selection interface (different columns)

-- Save current connection as session
\ss production_db

-- List saved sessions
\s

-- Connect to saved session
\s production_db

-- Delete saved session
\sd old_session

-- List recent connections
\r

-- Clear connection history
\rc

-- Toggle expanded display (vertical layout)
\x

-- Before (horizontal):
-- | id | name        | email                |
-- |----|-------------|----------------------|
-- | 1  | John Smith  | john@example.com     |

-- After (vertical):
-- -[ RECORD 1 ]-------------------
-- id    | 1
-- name  | John Smith
-- email | john@example.com

-- Toggle EXPLAIN mode
\e

-- Now all queries show execution plans
SELECT * FROM users WHERE email = 'user@example.com';

-- Output includes:
-- ○ Execution Time: 1.23 ms • Planning Time: 0.15 ms
-- Index Scan
-- │ Optimized lookup using email_idx
-- │ ○ Duration: 0.96 ms • Cost: 4 • Rows: 1
-- └► id • name • email • created_at

-- Copy EXPLAIN output to clipboard
\ecopy

-- Works with any query result - run query first, then copy
SELECT * FROM users LIMIT 5;
\ecopy  -- Copies the table output

-- PostgreSQL JSON/JSONB with syntax highlighting
SELECT user_profile FROM users WHERE id = 1;
-- {"name": "John", "settings": {"theme": "dark"}, "tags": ["admin", "power_user"]}

-- PostgreSQL arrays with proper formatting
SELECT tags FROM posts WHERE published = true;
-- {technology, programming, rust, database}

-- Geographic data (GeoJSON) with coordinate summaries
SELECT location FROM stores;
-- GeoJSON Point: [-122.419, 37.775] (San Francisco, CA)

-- Vector data (pgvector extension)
SELECT embedding FROM documents WHERE title = 'Machine Learning Basics';
-- Vector[384]: [0.123, -0.456, 0.789, ...] (similarity search ready)

-- Array format detection
SELECT ARRAY[1,2,3]::integer[];     -- Displays as: {1, 2, 3}
SELECT '[1,2,3]'::json;             -- Displays as: [1, 2, 3] (JSON syntax)

-- JSONB with syntax highlighting
SELECT '{"status": "active"}'::jsonb;
-- {
--   "status": "active"  ✓ (formatted with colors)
-- }

-- Tuple formatting
SELECT (1, 'hello', [1,2,3]) AS complex_tuple;
-- Tuple(3): (1, "hello", [1, 2, 3])

-- Display mode control
\cd                     -- Show current display mode
\cd full               -- Set to full mode
\cd truncated          -- Set to truncated mode
\cd summary            -- Set to summary mode
\cd viz                -- Set to visualization mode

-- JSON formatting toggle
\cdj                   -- Toggle JSON pretty printing on/off

-- E-commerce analytics with complex JSON
SELECT
    order_id,
    customer_data,           -- JSON with nested preferences
    item_details,           -- Array of product objects
    shipping_address        -- GeoJSON location data
FROM orders
WHERE created_at > current_date - interval '1 day';

-- Results automatically formatted:
-- customer_data: {"id": 12345, "tier": "gold", "preferences"...} [234 chars]
-- item_details: Array[3]: [{"sku": "ABC-123", "qty": 2...}]
-- shipping_address: GeoJSON Point: [-74.006, 40.714] (New York, NY)

-- Vector similarity search with readable results
SELECT
    title,
    content_summary,
    embedding                -- Vector[1536] from OpenAI embeddings
FROM documents
ORDER BY embedding <-> '[0.1, -0.2, 0.3, ...]'::vector
LIMIT 5;

-- Results show:
-- embedding: Vector[1536]: [0.123, -0.456, 0.789, ...] (cosine similarity ready)

-- Spatial data with intelligent coordinate formatting
SELECT
    store_name,
    location,               -- PostGIS geometry
    service_area           -- GeoJSON polygon
FROM retail_locations
WHERE ST_DWithin(location, ST_Point(-122.4194, 37.7749), 1000);

-- Results show:
-- location: POINT(-122.4194 37.7749) → San Francisco, CA
-- service_area: GeoJSON Polygon: 4 vertices, ~2.3 km²

-- DBCrust maintains query history per session
-- Use up/down arrows to navigate history

-- Or access history programmatically
\history  -- Show recent queries

-- DBCrust handles transactions transparently
BEGIN;
UPDATE users SET last_login = NOW() WHERE id = 123;
-- Connection remains in transaction state
SELECT * FROM users WHERE id = 123;  -- Shows updated data
COMMIT;

-- Load and execute SQL files
\i /path/to/script.sql

-- Save queries to files
\w /path/to/query.sql

-- Show current configuration
\config

-- Show specific section
\config database
\config display

-- Some settings can be changed at runtime
\set show_execution_time true
\set max_column_width 80

-- Check available queries
\n

-- Check specific scope
\n --scope global
\n --scope postgres

-- Check parameter syntax - use $1, $2, not {1}, {2}
\ns test_query SELECT * FROM users WHERE id = $1;  -- Correct

-- Check complex display settings
\cd  -- Show current display mode

-- Try different modes
\cd full      -- Show complete data
\cd summary   -- Show structure overview
\cdj          -- Toggle JSON pretty printing