PG Basic Commands

==============================================================================================================

Create a User

 

 

postgres=# create user user_name with encrypted password 'mypassword';

CREATE USER s2user WITH PASSWORD '***************';

CREATE USER s2user WITH PASSWORD '***************' VALID UNTIL 2040-12-01;
CREATE USER s2user SUPERUSER LOGIN PASSWORD '12345';
CREATE USER s2user SUPERUSER LOGIN PASSWORD '12345';

 

postgres=# grant all privileges on database sample_db to user_name;

 

==============================================================================================================

Insert a huge Data in a Table:

 ---Create table and Insert data 

CREATE TABLE COMPANY(   

   NAME           CHAR(2000) ,

   AGE            INT     NOT NULL,

   ADDRESS        CHAR(2000),

   SALARY         REAL,

   JOIN_DATE   DATE

);

INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY,JOIN_DATE) VALUES (1, 'Paul', 32, 'California', 20000.0,'2001-07-13');

 do $$

declare 

   counter integer := 0;

begin

   while counter < 5000 loop

      raise notice 'Counter %', counter;

  counter := counter + 1;

  

  insert into COMPANY(name,age)

  select 'Abhishek Yadav', 100;

  

   end loop;

end$$;

==============================================================================================================

Running/Active SQL

 

acweb=# select datname, usename , state backendtype , query from pg_stat_activity;

 datname | usename  | backendtype |                                   query

---------+----------+-------------+----------------------------------------------------------------------------

         |          |             |

         | postgres |             |

 acweb   | myuser   | active      | select datname, usename , state backendtype , query from pg_stat_activity;

         |          |             |

         |          |             |

 

 

select datname, usename , state backendtype,backend_type , query from pg_stat_activity where backend_type = 'client backend';

 datname | usename  | backendtype |  backend_type  |                                                             query

 

---------+----------+-------------+----------------+----------------------------------------------------------------------------------------------------------------------------

---

 i3      | postgres | idle        | client backend | select * from i3_emp;

 acweb   | myuser   | active      | client backend | select datname, usename , state backendtype,backend_type , query from pg_stat_activity where backend_type = 'client backend

';

(2 rows)

 

==============================================================================================================

To check if a query is waiting for another query:

 

 

SELECT datname, usename, wait_event_type, wait_event, backend_type, query

FROM pg_stat_activity

WHERE wait_event_type IS NOT NULL

AND wait_event_type NOT IN ('Activity', 'Client');

 

 

First Query Window

i3=# begin;

BEGIN

i3=*# update i3_emp set id=6 where id=2;

UPDATE 2

i3=*#

Second Query Window

i3=# update i3_emp set id=7 where id=2;

/*This query will keep executing*/

 

Third Query Window. Check the query status.

 

postgres=# SELECT datname, usename, wait_event_type, wait_event, backend_type, query

FROM pg_stat_activity

WHERE wait_event_type IS NOT NULL

AND wait_event_type NOT IN ('Activity', 'Client');

 

 datname | usename | wait_event_type |  wait_event   |  backend_type  |               query

---------+---------+-----------------+---------------+----------------+------------------------------------

 i3      | myuser  | Lock            | transactionid | client backend | update i3_emp set id=7 where id=2;

(1 row)

 

 

==============================================================================================================

Kill a  query pID:

pg_cancel_backend(pid)

 

EXAMPLE

 

--Identify PID of the SQL.

 

postgres=# select pid,client_hostname,usename,query from pg_stat_activity where state='active';

 

  pid   | client_hostname | usename  |                                        query

--------+-----------------+----------+--------------------------------------------------------------------------------------

 117810 |                 | postgres | select pid,client_hostname,usename,query from pg_stat_activity where state='active';

 116992 |                 | myuser   | update i3_emp set id=7 where id=2;

 

SELECT pg_cancel_backend(<pid ofthe process>)

 

 

postgres=# SELECT pg_cancel_backend(116992);

 pg_cancel_backend

-------------------

 t

(1 row)

 

i3=# update i3_emp set id=7 where id=2;

ERROR:  canceling statement due to user request

CONTEXT:  while updating tuple (0,2) in relation "i3_emp"

i3=#

1

 

==============================================================================================================

Knowing whether anybody is using a specific table

 

CREATE TEMPORARY TABLE tmp_stat_user_tables AS

SELECT * FROM pg_stat_user_tables;

 

 

SELECT * FROM pg_stat_user_tables n

 JOIN tmp_stat_user_tables t

 ON n.relid=t.relid

 AND (n.seq_scan,n.idx_scan,n.n_tup_ins,n.n_tup_upd,n.n_tup_del)

 <> (t.seq_scan,t.idx_scan,t.n_tup_ins,t.n_tup_upd,t.n_tup_del);

 

==============================================================================================================

Usage of disk space by temporary data

 

select current_setting('temp_tablespaces');

 current_setting

-----------------