Difference between revisions of "Useful PostgreSQL Queries"
From Observer GigaFlow Support | VIAVI Solutions Inc.
Kevin Wilkie (Talk | contribs) (Created page with "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...") |
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; | ||
+ | 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; |
Revision as of 13:13, 13 September 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;
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;