Postgresql important commands for PSQL DB management and queries

Postgres important commands.md

Login to PostgreSQL from terminal with Postgres DB user

Note that, in following lines, you have first entered into postgres user't terminal (Of your OS) root Then by using psql command, you have entered into the postgres DB user's shell

[mainul35@localhost ~]$ sudo su postgres
bash-5.0$ psql
could not change directory to "/home/mainul35": Permission denied
psql (11.7)
Type "help" for help.

postgres=# 

Exit from Postgres DB user shell and Postgres system user

postgres=# \q
bash-5.0$ exit;
exit
[mainul35@localhost ~]$ 

Enter again and show databases

[mainul35@localhost ~]$ sudo su postgres
bash-5.0$ psql
could not change directory to "/home/mainul35": Permission denied
psql (11.7)
Type "help" for help.

postgres=# \l
                                        List of databases
         Name          |  Owner   | Encoding |   Collate   |    Ctype    |   Acc
ess privileges   
-----------------------+----------+----------+-------------+-------------+------
-----------------
 chat_app              | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 demonstrating_oauth   | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 mainul35              | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 postgres              | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 qa_board              | mainul35 | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 student_course_module | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0             | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/po
stgres          +
                       |          |          |             |             | postg
res=CTc/postgres
 template1             | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/po
stgres          +
                       |          |          |             |             | postg
res=CTc/postgres
 victoryapp            | mainul35 | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
(9 rows)

postgres=# 

Connect to some Database

postgres=# \c chat_app
You are now connected to database "chat_app" as user "postgres".
chat_app=# 

Show schemas

chat_app=# \dn
  List of schemas
  Name  |  Owner   
--------+----------
 public | postgres
(1 row)

chat_app=# 

Create Schema and change owner

chat_app=# create schema chat_app;
CREATE SCHEMA
chat_app-# \dn
   List of schemas
   Name   |  Owner   
----------+----------
 chat_app | postgres
 public   | postgres
(2 rows)

chat_app=# ALTER SCHEMA chat_app OWNER TO mainul35;
ALTER SCHEMA
chat_app=# \dn
   List of schemas
   Name   |  Owner   
----------+----------
 chat_app | mainul35
 public   | postgres
(2 rows)

Create Table under chat_app schema and show them

chat_app=# \dt chat_app.
                List of relations
  Schema  |        Name        | Type  |  Owner   
----------+--------------------+-------+----------
 chat_app | ewsd_tbl_authority | table | postgres
 chat_app | user_auth_details  | table | postgres
(2 rows)

chat_app=# create table chat_app.test(id integer);
CREATE TABLE
chat_app=# \dt chat_app.
                List of relations
  Schema  |        Name        | Type  |  Owner   
----------+--------------------+-------+----------
 chat_app | ewsd_tbl_authority | table | postgres
 chat_app | test               | table | postgres
 chat_app | user_auth_details  | table | postgres
(3 rows)

chat_app=# 

Drop Table

chat_app=# drop table chat_app.test;
DROP TABLE
chat_app=# \dt chat_app.
                List of relations
  Schema  |        Name        | Type  |  Owner   
----------+--------------------+-------+----------
 chat_app | ewsd_tbl_authority | table | postgres
 chat_app | user_auth_details  | table | postgres
(2 rows)

chat_app=#