Postgres Query Sample¶

SELECT schema_name, 
       pg_size_pretty(sum(table_size)::bigint),
       (sum(table_size) / pg_database_size(current_database())) * 100
FROM (
  SELECT pg_catalog.pg_namespace.nspname as schema_name,
         pg_relation_size(pg_catalog.pg_class.oid) as table_size
  FROM   pg_catalog.pg_class
     JOIN pg_catalog.pg_namespace ON relnamespace = pg_catalog.pg_namespace.oid
) t
GROUP BY schema_name
ORDER BY schema_name

select * from INFORMATION_SCHEMA.COLUMNS where table_schema = 'table_schema' and table_name = 'table' and column_name = 'column' and is_updatable = 'YES'; 
select * from INFORMATION_SCHEMA.tables  where table_schema = 'table_schema' and table_type = 'BASE TABLE';

select relname,last_vacuum, last_autovacuum, last_analyze, vacuum_count, autovacuum_count,
  last_autoanalyze from pg_stat_user_tables where schemaname = 'schemaname' order by relname ASC;

select * from pg_indexes where tablename not like 'pg%' and schemaname = 'schemaname';

-- need plugin pg_dirtyread
 SELECT * FROM pg_dirtyread('util.deps_backup_ddl')
t(deps_id int, deps_view_schema varchar(255), deps_view_name varchar(255), deps_ddl_to_run text );

select util.deps_save_and_drop_dependencies('table_schema', 'table');
select util.deps_restore_dependencies('table_schema', 'table');

-- Method 1
DELETE FROM table  T1
          USING table T2
            WHERE  T1.ctid    < T2.ctid       -- delete the "older" ones
              AND  T1.date    = T2.date       -- list columns that define duplicates
              AND  T1.ne_name = T2.ne_name
              AND  T1.nr_cell_id = T2.nr_cell_id
              AND  T1.gnodeb_id = T2.gnodeb_id
              AND  T1.cell_id = T2.cell_id;

-- Method 2
DELETE from table a using table  b where a=b and a.ctid < b.ctid;