notes

Unnamed repository; edit this file 'description' to name the repository.
Log | Files | Refs

psql.txt (1732B)

      1 ==========================================================================
      2 PSQL
      3 ==========================================================================
      4 
      5 Enter interactive shell of a dockerized container:
      6 
      7 docker exec -it <container-name> psql -U username -d main_db
      8 
      9 ==========================================================================
     10 
     11 Connect to remote database:
     12 
     13 psql "postgresql://doadmin:PASSWORD@db-postgresql-lon1-12345-do-user-1234567-0.b.db.ondigitalocean.com:25060/defaultdb?sslmode=require"
     14 
     15 ==========================================================================
     16 
     17 Queries:
     18 
     19 Show hidden/system schemas:
     20 \dnS
     21 
     22 Show hidden/system tables (all schemas):
     23 \dtS *.*
     24 
     25 Show hidden/system tables in one schema:
     26 \dtS pg_catalog.*
     27 
     28 Describe a table including system columns:
     29 \dS+ table_name
     30 
     31 List all schemas via SQL:
     32 SELECT nspname FROM pg_namespace ORDER BY nspname;
     33 
     34 List all tables via SQL (including system):
     35 SELECT schemaname, tablename FROM pg_tables ORDER BY schemaname, tablename;
     36 
     37 ==========================================================================
     38 
     39 Connect and execute:
     40 psql -h localhost -U myuser -d mydb -c "SELECT * FROM users LIMIT 5"
     41 
     42 Export to CSV:
     43 psql -h localhost -U myuser -d mydb -c "SELECT * FROM users" --csv > users.csv
     44 
     45 Batch Query (no header, comma separated):
     46 psql -h localhost -U myuser -d mydb -t -A -F',' -c "SELECT id, email FROM users"
     47 
     48 ==========================================================================
     49 
     50 Delete multiple things:
     51 
     52 BEGIN;
     53 DELETE FROM git_file_actor_sessions
     54 WHERE project_id IN (
     55     SELECT p.id FROM projects p
     56     JOIN departments d ON p.department_id = d.id
     57     WHERE d.organisation_id = <org_id>
     58 );
     59 DELETE FROM organisations WHERE id = <org_id>;
     60 COMMIT;