Difference between revisions of "Useful PostgreSQL Queries"
From Observer GigaFlow Support | VIAVI Solutions Inc.
Kevin Wilkie (Talk | contribs) |
Kevin Wilkie (Talk | contribs) |
||
Line 7: | Line 7: | ||
ORDER BY pg_relation_size(C.oid) DESC | ORDER BY pg_relation_size(C.oid) DESC | ||
LIMIT 20; | 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 | 8.4 fixes | ||
Revision as of 10:18, 23 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;
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; $$;