edoceo

PostgreSQL Tricks

Find Table Size

SELECT pg_database.datname,pg_size_pretty(pg_database_size(pg_database.datname)) AS size
FROM pg_database where datname='database_name';

Batch Update Table Owners

Using only SQL to change owners of all tables in a database.

select 'alter table '|| tablename ||' owner to ;' from pg_tables where schemaname = 'public';

Or, using the slightly more dangerous:

UPDATE pg_class SET 
    relowner = (SELECT oid FROM pg_roles WHERE rolname = '$USER')
WHERE relname IN 
    (SELECT relname FROM pg_class, pg_namespace
        WHERE pg_namespace.oid = pg_class.relnamespace AND pg_namespace.nspname = 'public');

More Tricks

Loading Comments from Disqus...