Monday, 27 January 2014

Handy Queries of PostgreSQL:

Let us start from the basic queries..
to know version:
select version();
to know size of database:
SELECT pg_size_pretty(pg_database_size('mydatabasename')) As fulldbsize;
to know all catalog views:
\dt pg_catalog.*

Looks I am at very basic level. Dont mind, let me go some advance level.. :-)

Query to find Parameters changes for a table:
===============================
SELECT c.relname, pg_catalog.array_to_string(c.reloptions || array(select 'toast.' || x from pg_catalog.unnest(tc.reloptions) x), ', ')
FROM pg_catalog.pg_class c
 LEFT JOIN pg_catalog.pg_class tc ON (c.reltoastrelid = tc.oid)
WHERE c.relname = 'test'

Long Running Queries:
select (now() - query_start)  as query_time_taken,* from pg_stat_activity where xact_start is not null and  ( current_query ilike '%select%' or current_query ilike '
%update%' or current_query ilike '%delete%' or current_query ilike '%truncate%' or current_query ilike '%drop%') and (now() - query_start) > interval '60 min';

Delete Duplicate Values in a table using CTID (pseudo column):
DELETE FROM dupes a
WHERE a.ctid <> (SELECT min(b.ctid)
                 FROM   dupes b
                 WHERE  a.key = b.key);

Want to GRANT privileges on all tables !!!, dont worry, here is the query to generate script:
SELECT 'grant select,update,usage on '||c.relname||' to username;' FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','') AND n.nspname='schemaname' AND pg_catalog.pg_get_userbyid(c.relowner)='username';

Ohh Good !! you have granted privileges on tables, now you want to check ??.. here is a query:
SELECT n.nspname as "Schema",
  c.relname as "Name",
  CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'S' THEN 'sequence' END as "Type",
  pg_catalog.array_to_string(c.relacl, E'\n') AS "Access privileges",
  pg_catalog.array_to_string(ARRAY(
    SELECT attname || E':\n  ' || pg_catalog.array_to_string(attacl, E'\n  ')
    FROM pg_catalog.pg_attribute a
    WHERE attrelid = c.oid AND NOT attisdropped AND attacl IS NOT NULL
  ), E'\n') AS "Column access privileges"
FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r') AND pg_catalog.pg_get_userbyid(c.relowner)='username' AND n.nspname='schemaname';

Query to find all functions with arguments:
SELECT n.nspname || '.' || p.proname || '(' || pg_catalog.oidvectortypes(p.proargtypes) || ')' as FunctionName,usename as OWNER FROM pg_proc p LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace, pg_user u WHERE p.prorettype <> 'pg_catalog.cstring'::pg_catalog.regtype AND p.proargtypes[0] <> 'pg_catalog.cstring'::pg_catalog.regtype AND NOT p.proisagg AND pg_catalog.pg_function_is_visible(p.oid) AND p.proowner=u.usesysid AND n.nspname not in ('pg_catalog','sys');

-- OR

select proname||'('||pg_get_function_arguments(pg_proc.oid)||')' as function_arguments,usename,nspname from pg_proc,pg_user,pg_namespace where  proowner=pg_user.usesysid and pronamespace=pg_namespace.oid and usename<>nspname and nspname !~ '^pg_catalog|^information_schema|^sys';

Hmm.. You have granted some privileges to a user, but forgot to notedown. Here is a query to find the privileges of a user on objects:
SELECT n.nspname as "Schema",
    c.relname as "Name",
    CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'S' THEN 'sequence' WHEN 'f' THEN 'foreign table' END as "Type",
    pg_catalog.array_to_string(c.relacl, E'\n') AS "Access privileges",
    pg_catalog.array_to_string(ARRAY(
      SELECT attname || E':\n  ' || pg_catalog.array_to_string(attacl, E'\n  ')
      FROM pg_catalog.pg_attribute a
      WHERE attrelid = c.oid AND NOT attisdropped AND attacl IS NOT NULL
    ), E'\n') AS "Column access privileges"
  FROM pg_catalog.pg_class c
       LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
  WHERE c.relkind IN ('r', 'v', 'S', 'f')
    AND n.nspname !~ '^pg_' AND pg_catalog.pg_table_is_visible(c.oid) and pg_catalog.pg_get_userbyid(c.relowner)='enterprisedb'
  ORDER BY 1, 2;

Ok, looks you have migrated your tables from one database(server) to another database(server), hmm.. good. But now you should check if all tables and their data are migrated sucessfully or not. Ah, something difficult but you can try below query:
To get list of all tables and their row count:
SELECT
pgClass.relname AS tableName,
pgClass.reltuples AS rowCount
FROM
pg_class pgClass
LEFT JOIN
pg_namespace pgNamespace ON (pgNamespace.oid = pgClass.relnamespace)
WHERE
pgNamespace.nspname NOT IN ('pg_catalog', 'information_schema') AND
pgClass.relkind='r';

Want to check tables in each user defined schema, here you go:
SELECT n.nspname as "Schema",
  count(c.relname) as "Name"
FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','')
      AND n.nspname <> 'pg_catalog'
      AND n.nspname <> 'information_schema'
      AND n.nspname !~ '^pg_toast'
  AND pg_catalog.pg_table_is_visible(c.oid)
 group by n.nspname;

You want a alternative of "sysdate" function in PostgteSQL like Oracle and Postgres Plus Advance Server, good thought .. Keep it up !!..
edb=# \df sysdate
                                 List of functions

   Schema   |  Name   |      Result data type       | Argument data types |  Type 
------------+---------+-----------------------------+---------------------+--------
pg_catalog | sysdate | timestamp without time zone |                     | normal
(1 row)
--However in PG, we will have to create "sysdate" function without paranthesis where PG does not allow us to create the function without parathesis.

postgres=# create function pg_catalog.sysdate returns date language sql as 'SELECT PG_CATALOG.current_date';

ERROR:  syntax error at or near "returns"

LINE 1: create function pg_catalog.sysdate returns date language sql...

--If we create with paranthesis, then we should call it with parathensis.

postgres=# create or replace function pg_catalog.sysdate() returns date language sql as 'SELECT current_date';
CREATE FUNCTION
postgres=# select sysdate;
ERROR:  column "sysdate" does not exist
LINE 1: select sysdate;
               ^
postgres=# select sysdate();
  sysdate 
------------
 2013-08-01

Use this query to read total number of transactions executed in all databases:
SELECT sum(xact_commit+xact_rollback) FROM pg_stat_database;

If you want the same counter for just one database, use:
SELECT xact_commit+xact_rollback FROM pg_stat_database WHERE datname = 'mydb';

By mistake you have created all tables in UPPER CASE !!!.. Here is a query to generate a script to change or rename all table names to LOWER CASE:
SELECT 'alter table "'||c.relname||'" rename to '||lower(c.relname)||';'
FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind ='r'
      AND n.nspname='public'
ORDER BY 1;

Oops even column names are also in UPPER CASE, No worries, here is a query to generate a script to change or rename all columns of a table:
For One table:
SELECT
        'alter table "'||c.relname||'" rename "'||a.attname||'" to '||lower(a.attname)||';'
FROM
        pg_class c
        JOIN pg_attribute a ON a.attrelid = c.oid
        JOIN pg_type t ON a.atttypid = t.oid
        LEFT JOIN pg_catalog.pg_constraint r ON c.oid = r.conrelid
                AND r.conname = a.attname
WHERE
        c.relnamespace = (select oid from pg_namespace where nspname='schemaname')
        AND a.attnum > 0 AND c.relkind ='r'
      
ORDER BY a.attnum

For all tables in a schema:
SELECT 
        'altet table "'||c.relname||'" rename "'||a.attname||'" to '||lower(a.attname)||';'
FROM
        pg_class c
        JOIN pg_attribute a ON a.attrelid = c.oid
        JOIN pg_type t ON a.atttypid = t.oid
        LEFT JOIN pg_catalog.pg_constraint r ON c.oid = r.conrelid
                AND r.conname = a.attname
WHERE
        c.relnamespace = (select oid from pg_namespace where nspname='schemaname')
        AND a.attnum > 0
      
ORDER BY a.attnum


3 comments:

  1. Hola muy buen post, una pregunta como sería para identificar en que esquema se encuentra registrado mi usuario? y de nuevo gracias.

    ReplyDelete