Useful PostgreSQL commands
9:38 AM1. How do I get a list of databases in a Postgresql database ?
postgres=# \l
2. How do I get a list of all the tables in a Postgresql database?
postgres=# \d or postgres=# \dt or postgres=# \dt+
3. Getting help on postgreSQL commands
postgres=# \? postgres=# \? create postgres=# \? create index
4. Creating a DB and Table
shell> createdb testdb /*this is from linux shell*/
OR
postgres=# create database testdb; /*this is from postgres shell*/
postgres=# create table test_table( id int primary key not null, name text not null,
age int not null, address char (50), salary real);
5. Describe a Table
postgres=# \d test_table;
6. How to list available users
postgres=# SELECT * from pg_catalog.pg_user;
7. To get a list of user roles
postgres=#\du
8. How to give and see grant on a particular database
postgres=#grant all(rolename) on testdb(dbname) to test_user(username);
testdb=#\z or \dp
rolename=xxxx -- privileges granted to a role
=xxxx -- privileges granted to PUBLIC
r -- SELECT ("read")
w -- UPDATE ("write")
a -- INSERT ("append")
d -- DELETE
D -- TRUNCATE
x -- REFERENCES
t -- TRIGGER
X -- EXECUTE
U -- USAGE
C -- CREATE
c -- CONNECT
T -- TEMPORARY
arwdDxt -- ALL PRIVILEGES (for tables, varies for other objects)
9. Dropping a database or table
postgres=# drop database testdb; testdb=# drop table test_table; or bash-4.1> dropdb testdb
10. Show processlist
postgres=# select * from pg_stat_activity;
0 comments