PostgreSQL Learning Guide

# Create the config file
nano ~/.psqlrc

# Copy config to postgres user
sudo cp ~/.psqlrc /var/lib/postgresql/.psqlrc
sudo chown postgres:postgres /var/lib/postgresql/.psqlrc

# Connect to PostgreSQL
sudo -u postgres psql

-- ================================
-- Beautiful PostgreSQL Configuration
-- ================================

-- Turn off welcome messages during setup
\set QUIET 1

-- ================================
-- FORMATTING
-- ================================

-- Use Unicode box-drawing characters for tables
\pset linestyle unicode
\pset border 2

-- Show NULL values clearly
\pset null '∅'

-- Auto-expand tables when they're too wide
\x auto

-- Better table format
\pset format aligned

-- Show query execution time
\timing on

-- ================================
-- COLORS & PROMPT
-- ================================

-- Colorful prompt with database name
\set PROMPT1 '%[%033[1;34m%]%n%[%033[0m%]@%[%033[1;36m%]%/%[%033[0m%]%R%[%033[1;34m%]%#%[%033[0m%] '

-- Continuation prompt for multi-line queries
\set PROMPT2 '%[%033[1;34m%]...%[%033[0m%] '

-- ================================
-- HISTORY
-- ================================

-- Separate history file per database
\set HISTFILE ~/.psql_history- :DBNAME

-- Don't save duplicate commands
\set HISTCONTROL ignoredups

-- Increase history size
\set HISTSIZE 10000

-- ================================
-- ERROR HANDLING
-- ================================

-- Show detailed error messages
\set VERBOSITY verbose

-- Stop on first error in scripts
\set ON_ERROR_STOP on

-- Show error rollback info
\set ON_ERROR_ROLLBACK interactive

-- ================================
-- HELPFUL SHORTCUTS
-- ================================

-- Quick table info
\set info 'SELECT schemaname, tablename, tableowner FROM pg_tables WHERE schemaname NOT IN (\'pg_catalog\', \'information_schema\') ORDER BY tablename;'

-- Database size
\set dbsize 'SELECT pg_database.datname, pg_size_pretty(pg_database_size(pg_database.datname)) AS size FROM pg_database ORDER BY pg_database_size(pg_database.datname) DESC;'

-- Table sizes
\set tablesize 'SELECT tablename, pg_size_pretty(pg_total_relation_size(schemaname||''.''||tablename)) AS size FROM pg_tables WHERE schemaname NOT IN (\'pg_catalog\', \'information_schema\') ORDER BY pg_total_relation_size(schemaname||''.''||tablename) DESC;'

-- Active queries
\set activity 'SELECT pid, usename, application_name, client_addr, state, query FROM pg_stat_activity WHERE state != \'idle\' ORDER BY query_start DESC;'

-- Show all databases with sizes
\set databases 'SELECT d.datname as "Database", pg_catalog.pg_get_userbyid(d.datdba) as "Owner", pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(d.datname)) as "Size" FROM pg_catalog.pg_database d ORDER BY pg_catalog.pg_database_size(d.datname) DESC;'

-- Turn messages back on
\set QUIET 0

-- Welcome message
\echo '\n'
\echo '╔════════════════════════════════════════╗'
\echo '║   PostgreSQL - Ready! 🐘              ║'
\echo '╚════════════════════════════════════════╝'
\echo ''
\echo 'Shortcuts available:'
\echo '  :info      - List all tables'
\echo '  :dbsize    - Show database sizes'
\echo '  :tablesize - Show table sizes'
\echo '  :activity  - Show active queries'
\echo '  :databases - List all databases'
\echo ''
\echo 'Commands:'
\echo '  \\l         - List databases'
\echo '  \\c dbname  - Connect to database'
\echo '  \\dt        - List tables'
\echo '  \\d table   - Describe table'
\echo '  \\q         - Quit'
\echo '\n'

:info       -- List all your tables
:dbsize     -- Show database sizes
:tablesize  -- Show table sizes
:activity   -- Show running queries
:databases  -- List all databases

# Connect as postgres user
sudo -u postgres psql

# Connect to specific database
sudo -u postgres psql -d database_name

# Connect as specific user
psql -U username -d database_name

# Connect to remote database
psql -h hostname -U username -d database_name

\l                    -- List all databases
\c database_name      -- Connect to database
\q                    -- Quit psql

CREATE DATABASE mydb;
DROP DATABASE mydb;

\dt                   -- List all tables
\dt+                  -- List tables with sizes
\d table_name         -- Describe table structure
\d+ table_name        -- Detailed table description

-- Copy table structure
CREATE TABLE new_table (LIKE old_table);

\dn                   -- List schemas
\dn+                  -- List schemas with details

CREATE SCHEMA schema_name;
DROP SCHEMA schema_name CASCADE;
SET search_path TO schema_name;

\du                   -- List users and roles
\du+                  -- Detailed user information

CREATE USER username WITH PASSWORD 'password';
ALTER USER username WITH SUPERUSER;
DROP USER username;

-- GOOD: Normalized
CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    phone VARCHAR(20)
);

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    price DECIMAL
);

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);