Some Basic PostgreSQL Commands

This post contains some basic postgresql commands. I will add/edit this post with additional commands over time.

• mysql: SHOW TABLES postgresql: \d postgresql: SELECT table_name FROM information_schema.tables WHERE table_schema = 'public';  
• mysql: SHOW DATABASES postgresql: \l postgresql: SELECT datname FROM pg_database;   
• mysql: SHOW COLUMNS postgresql: \d table postgresql: SELECT column_name FROM information_schema.columns WHERE table_name ='table';  
• mysql: DESCRIBE TABLE postgresql: \d+ table postgresql: SELECT column_name FROM information_schema.columns WHERE table_name ='table'; 

Alter Role 

Add replication 

•  alter role twalters replication; 
• 
  

Remove Replication 

View Active SQL Queries  

• Use pg_stat_activity table which will show you what activity is currently occurring on the database server 
• -bash-4.1$ psql -d dbparty -c "SELECT datname,usename,procpid,client_addr,waiting,query_start,current_query FROM pg_stat_activity"  

Database Connection 

• su - postgresql; psql databaseName -U username  

Common Commands 

• \l : list databases 
•  \d  : list tables in db  
• \d table-name  : describe table 
• select * from table-name  : lists table contents  

Import SQL Dump into Postgres 

• psql databasename < data_base_dump 

How to create a Database and Assign Permissions 

• switch to postgres user 
  • createuser (and follow prompts) 
  • createdb databasename  
• Use psql to set password and grant access 
  • postgres=# alter user username with encrypted password 'password';  ALTER ROLE  postgres=# grant all privileges on database databasename to username;  GRANT