notes

Log | Files | Refs | README

psql.md (2622B)

      1 # PostgreSQL psql Commands Reference
      2 
      3 ## Schema Exploration Workflow
      4 
      5 ### 1. Listing Schemas
      6 ```sql
      7 -- List all user schemas
      8 \dn
      9 
     10 -- List all schemas with details
     11 \dn+
     12 
     13 -- Direct SQL query to list all schemas
     14 SELECT nspname FROM pg_namespace ORDER BY nspname;
     15 ```
     16 
     17 ### 2. Finding Specific Schema Objects
     18 ```sql
     19 -- List tables in a specific schema
     20 \dt schema_name.*
     21 
     22 -- List all tables in 'saps' schema
     23 \dt saps.*
     24 
     25 -- List views in schema
     26 \dv saps.*
     27 
     28 -- List functions in schema
     29 \df saps.*
     30 ```
     31 
     32 ### 3. Schema Contents Query
     33 ```sql
     34 -- Query to see all tables in a specific schema (direct SQL)
     35 SELECT schemaname, tablename 
     36 FROM pg_tables 
     37 WHERE schemaname = 'saps';
     38 
     39 -- Or using system catalogs
     40 SELECT c.relname as tablename, n.nspname as schemaname
     41 FROM pg_class c
     42 JOIN pg_namespace n ON n.oid = c.relnamespace
     43 WHERE n.nspname = 'saps'
     44 AND c.relkind = 'r';
     45 ```
     46 
     47 ### 4. Working with Tables in Non-Default Schemas
     48 ```sql
     49 -- Issue: Table not found when querying directly
     50 SELECT * FROM auth_sessions;
     51 -- ERROR: relation "auth_sessions" does not exist
     52 
     53 -- Solution 1: Use fully qualified name
     54 SELECT * FROM saps.auth_sessions;
     55 
     56 -- Solution 2: Set search path
     57 SET search_path TO saps, public;
     58 SELECT * FROM auth_sessions;
     59 ```
     60 
     61 ### 5. Managing Search Path
     62 ```sql
     63 -- Check current search path
     64 SHOW search_path;
     65 
     66 -- Set search path to include saps schema
     67 SET search_path TO saps, public;
     68 
     69 -- Set search path with user schema first
     70 SET search_path = 'saps', '$user', public;
     71 ```
     72 
     73 ### 6. Session-Scoped Search Path Changes
     74 ```sql
     75 -- Use transaction block for temporary search path
     76 BEGIN;
     77 SET search_path = saps, public;
     78 SELECT * FROM auth_sessions;
     79 -- Other queries...
     80 COMMIT;
     81 ```
     82 
     83 ## Key Concepts
     84 
     85 ### Schema Resolution
     86 - PostgreSQL uses `search_path` to determine which schemas to search for objects
     87 - Objects in schemas not in `search_path` must be referenced with fully qualified names (`schema.table`)
     88 - Default `search_path` typically includes `public` and `$user`
     89 
     90 ### Common Meta-Commands
     91 - `\dn` - List schemas
     92 - `\dt` - List tables  
     93 - `\dv` - List views
     94 - `\df` - List functions
     95 - `\d` - Describe object (table, view, function)
     96 
     97 ### System Catalog References
     98 - `pg_namespace` - Contains schema information
     99 - `pg_tables` - Contains table information  
    100 - `pg_class` - Contains all relational objects
    101 
    102 ## Best Practices
    103 
    104 1. **Always qualify table names** when working with multiple schemas
    105 2. **Use `SET search_path`** sparingly and only when needed
    106 3. **Check `search_path`** before running queries to understand which schemas are being accessed
    107 4. **Document custom schemas** in your project documentation for team knowledge