June 12, 2019

Postgre: Useful Queries

A loose collection of simple scirpts used for db introspection.

Postgre: Useful Queries
-- list all schemas:
select * from pg_namespace;

-- list all tables:
SELECT * FROM pg_catalog.pg_tables;

-- list all users:
SELECT u.usename AS "Role name",
       CASE WHEN u.usesuper AND u.usecreatedb THEN CAST('superuser, create
database' AS pg_catalog.text)
            WHEN u.usesuper THEN CAST('superuser' AS pg_catalog.text)
            WHEN u.usecreatedb THEN CAST('create database' AS
                pg_catalog.text)
            ELSE CAST('' AS pg_catalog.text)
           END AS "Attributes"
FROM pg_catalog.pg_user u
ORDER BY 1;

SELECT pg_size_pretty( pg_database_size('typplan_db_prod1') );