Useful PostgreSQL Queries

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;