Useful PostgreSQL Queries
From Observer GigaFlow Support | VIAVI Solutions Inc.
Revision as of 10:18, 23 November 2017 by Kevin Wilkie (Talk | contribs)
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;
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; $$;