Useful PostgreSQL Queries

From Observer GigaFlow Support | VIAVI Solutions Inc.
Revision as of 16:40, 28 November 2017 by Kevin Wilkie (Talk | contribs)

Jump to: navigation, search

Finding the largest tables

SELECT nspname || '.' || relname AS "relation",
   pg_size_pretty(pg_relation_size(C.oid)) AS "size"
 FROM pg_class C
 LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
 WHERE nspname NOT IN ('pg_catalog', 'information_schema')
 ORDER BY pg_relation_size(C.oid) DESC
 LIMIT 20;

Empty tables

SELECT nspname || '.' || relname AS "relation",
pg_size_pretty(pg_relation_size(C.oid)) AS "size"
FROM pg_class  C 
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE nspname NOT IN ('pg_catalog', 'information_schema')
and relname like 'netflow_____%' 
and pg_relation_size(C.oid)=0
ORDER BY pg_relation_size(C.oid) asc;


8.4 fixes

CREATE FUNCTION modulus(
   dividend numeric,
   divisor numeric)
  RETURNS numeric AS
' DECLARE result numeric; temp numeric; BEGIN   temp := ABS(divisor);result  := MOD(dividend, temp);IF result < 0 THEN    result := result + temp;  END IF;  RETURN result;END;'
 LANGUAGE plpgsql VOLATILE
 COST 100;
ALTER FUNCTION modulus(numeric, numeric)
 OWNER TO myipfix;
CREATE FUNCTION modulus(
   dividend numeric,
   divisor integer)
 RETURNS numeric AS
' DECLARE result numeric; temp numeric; BEGIN   temp := ABS(divisor);result  := MOD(dividend, temp);IF result < 0 THEN    result := result + temp;  END IF;  RETURN result;END;'
 LANGUAGE plpgsql VOLATILE
 COST 100;
ALTER FUNCTION modulus(numeric, numeric)
 OWNER TO myipfix;

Foogun to remove tables en-mas e.g. select foogun('public','flowsec_151'); will remove all tables starting with flowsec_151

CREATE OR REPLACE FUNCTION foogun(IN _schema TEXT, IN _parttionbase TEXT) 
RETURNS void 
LANGUAGE plpgsql
AS
$$
DECLARE
    row     record;
BEGIN
    FOR row IN 
        SELECT
            table_schema,
            table_name
         FROM
            information_schema.tables
        WHERE
           table_type = 'BASE TABLE'
         AND
            table_schema = _schema
        AND
            table_name ILIKE (_parttionbase || '%')
    LOOP
        EXECUTE 'DROP TABLE ' || quote_ident(row.table_schema) || '.' || quote_ident(row.table_name);
        RAISE INFO 'Dropped table: %', quote_ident(row.table_schema) || '.' || quote_ident(row.table_name);
    END LOOP;
END;
$$;