Difference between revisions of "Useful PostgreSQL Queries"

Revision as of 12:12, 6 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;

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 footgun(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;
$$;