# psql

I always end up looking up the same few things every time I need to interact with psql:

==== Quit ====  
 \\q

==== Databases ====

== List ==

 \\l

== Switch ==  
 \\c dbname

== Rename ==  
 ALTER DATABASE old RENAME TO new;

==== Tables ====

== List ==

 \\dt

== Show details ==  
 \\d tablename  
   
   
==== Create db with user ====

 create database mydb;  
 create user myuser with encrypted password 'mypass';  
 grant all privileges on database mydb to myuser;

==== Change user pw ====  
 ALTER USER user\_name WITH encrypted password 'new\_password';

(remove ~/.psql\_history after)

==== Drop a user after reassigning privs ===  
 REASSIGN OWNED BY <olduser> TO <newuser>  
or  
 DROP OWNED BY <olduser>

also to be more thorough:

 REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA public FROM username;  
 REVOKE ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public FROM username;  
 REVOKE ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA public FROM username;  
 REVOKE ALL PRIVILEGES ON DATABASE database FROM username;  
 DROP USER username;

https://stackoverflow.com/questions/3023583/how-to-quickly-drop-a-user-with-existing-privileges

==== Dump a database ====  
 pg\_dump -C -h (ip) -p (port) -U username dbname > dump.sql

(omit -C if you want to create the db yourself)  
==== Restore a dump ====  
 psql dbname < dump.sql