Difference between revisions of "Useful PostgreSQL Queries"

From Observer GigaFlow Support | VIAVI Solutions Inc.
Jump to: navigation, search
(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...")
 
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;