Difference between revisions of "Useful PostgreSQL Queries"
From Observer GigaFlow Support | VIAVI Solutions Inc.
Kevin Wilkie (Talk | contribs) |
Kevin Wilkie (Talk | contribs) |
||
Line 39: | Line 39: | ||
OWNER TO myipfix; | 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) | CREATE OR REPLACE FUNCTION foogun(IN _schema TEXT, IN _parttionbase TEXT) | ||
RETURNS void | RETURNS void |
Revision as of 16:40, 28 November 2017
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; $$;