Skip to main content

View Active Temp table In PostgreSQL

Purpose: Work with Temp table, Some time developer have trouble handle temp table or forgot to drop temp table in function.

Use: By Using below query developer can know active temp table.

Query:

SELECT
    n.nspname as SchemaName
    ,c.relname as RelationName
    ,CASE c.relkind
    WHEN 'r' THEN 'table'
    WHEN 'v' THEN 'view'
    WHEN 'i' THEN 'index'
    WHEN 'S' THEN 'sequence'
    WHEN 's' THEN 'special'
    END as RelationType
    ,pg_catalog.pg_get_userbyid(c.relowner) as RelationOwner              
    ,pg_size_pretty(pg_relation_size(n.nspname ||'.'|| c.relname)) as RelationSize
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n              
                ON n.oid = c.relnamespace
WHERE  c.relkind IN ('r','s')
AND  (n.nspname !~ '^pg_toast' and nspname like 'pg_temp%')
ORDER BY pg_relation_size(n.nspname ||'.'|| c.relname) DESC



Comments

Post a Comment

Popular posts from this blog

IN,ANY And ALL In PostgreSQL

IN,ANY And ALL  IN ANY And ALL are Keyword-Operator, Which is Used In WHERE Clause of SQL Statements. IN , ANY And ALL Returns Multiple Raw As per conditions. USE OF IN in SQL Statments:  SELECT * FROM tablename WHERE columnname IN ('value1','value2','value3','value4') SELECT * FROM tablename WHERE columnname IN   ( SELECT columnname FROM tablename WHERE Conditions..) SELECT * FROM tablename WHERE (col1,col2) IN (('val1','val2'),('val11','val22'))   SELECT * FROM tablename WHERE (col1,col2) IN (SELECT col1,col2 FROM table WHERE Conditions..)   USE OF ANY and ALL in SQL Statments: Used with a WHERE or HAVING clause . The ANY operator returns true if any of the subquery values meet the condition. The ALL operator returns true if all of the subquery values meet the condition. ANY Syntax SELECT column_name(s) FROM table_name WHERE colu...
Post a Comment
Read more

All About Data Tuning in SQL

What does means data tuning? It is all about database performance. How you write your code for database, it is depend on. There is lots of trick to improve your performance. How you design your database that is also matters performance . How to Improve performance of SQL?    Some Of Points To Be Taken Care While Design or Develop SQL Database. Avoid number-to-character conversions because numbers and characters compare differently and lead to performance downgrade. While using SELECT statement, only fetch whatever information is required and avoid using * in your SELECT queries because it would load the system unnecessarily. Create your indexes carefully on all the tables where you have frequent search operations. Avoid index on the tables where you have less number of search operations and more number of insert and update operations. A full-table scan occurs when the columns in the WHERE clause do not have an index associated with them. You can avoid a ...
Post a Comment
Read more