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

-- 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