PostgreSQL Scripts Part-1

• Query to check lock in PostgreSQL:

SELECT t.relname, l.locktype, page, virtualtransaction, pid, mode, granted
FROM pg_locks l, pg_stat_all_tables t
WHERE l.relation = t.relid
ORDER BY relation asc;

• Query to remove lock in PostgreSQL:

SELECT pg_terminate_backend(11064)

• Find column name belong to which table in PostgreSQL like in our case we are using column name as activity:

select t.table_schema,
t.table_name
from information_schema.tables t
inner join information_schema.columns c on c.table_name = t.table_name
and c.table_schema = t.table_schema
where c.column_name like '%activity%'
and t.table_schema not in ('information_schema', 'pg_catalog')
and t.table_type = 'BASE TABLE'
order by t.table_schema;

• Find table name starting with some keyword like in our case it is titanic:

select table_schema,
table_name
from information_schema.tables
where table_name like '%titanic%'
and table_schema not in ('information_schema', 'pg_catalog')
and table_type = 'BASE TABLE'
order by table_name,
table_schema;