Post Gre SQL dataabase

 

# Setup the repository

#

/*

# Install the public key for the repository (if not done previously):

sudo curl https://www.pgadmin.org/static/packages_pgadmin_org.pub | sudo apt-key add

# Create the repository configuration file:

sudo sh -c 'echo "deb https://ftp.postgresql.org/pub/pgadmin/pgadmin4/apt/$(lsb_release -cs) pgadmin4 main" > /etc/apt/sources.list.d/pgadmin4.list && apt update'

#

# Install pgAdmin

#

# Install for both desktop and web modes:

sudo apt install pgadmin4

# Install for desktop mode only:

sudo apt install pgadmin4-desktop

# Install for web mode only: 

sudo apt install pgadmin4-web 

# Configure the webserver, if you installed pgadmin4-web:

sudo /usr/pgadmin4/bin/setup-web.sh

*/

Hierarchy tree - get all child of same table using parentid

WITH RECURSIVE parent("childId","childCode","childParentId" ) AS (

   Select "childId","childCode","childParentId"  FROM table Where "childParentId"=n

  UNION ALL

   SELECT p."childId", p."childCode", p."childParentId"

    FROM  table  p

INNER JOIN parent pr on p."childParentId" = pr."childId" 

  

)

SELECT * FROM parent   ORDER BY "childId"

CTE

WITH RECURSIVE t(n) AS (
    SELECT 1
  UNION ALL
    SELECT n+1 FROM t
)
SELECT n FROM t LIMIT 100;

Some useful command

Get stored procedure Text

SELECT prosrc FROM pg_proc WHERE proname = 'pname';

TRUNCATE TABLE invoices CASCADE;

TRUNCATE TABLE table_name RESTART IDENTITY;

Date:

select TO_CHAR(NOW(),'YYYY-MM-DD HH:MI:SS')

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

Check profiler 

select max_conn,used,res_for_super,max_conn-used-res_for_super res_for_normal 

from 

  (select count(*) used from pg_stat_activity) t1,

  (select setting::int res_for_super from pg_settings where name=$$superuser_reserved_connections$$) t2,

  (select setting::int max_conn from pg_settings where name=$$max_connections$$) t3

  

  select *  from pg_stat_activity

  

  SELECT 

    pg_terminate_backend(pid) 

FROM 

    pg_stat_activity 

WHERE 

    -- don't kill my own connection!

    pid <> pg_backend_pid()

  AND datname is null