postgres system dictionary pg_catalog - ghdrako/doc_snipets GitHub Wiki

Note: In PostgreSQL, A new database is initialized with a pre-defined set of schemas and those are: public: A default Schema; Tables will get created in public schema by default if the schema detail is not provided during the table creation.

information_schema:Views in information_schema are based on pg_catalog.INFORMATION_SCHEMA data dictionary is SQL standard and doesn't change between postgres versions. Contains information about the database objects defined in the current database. An information schema, named information_schema, contains information about tables, columns, users, and other aspects of the database. Because the information schema does not contain information specific to the database management software, the system catalog should be checked for more detailed information.And pg_catalog is much faster!!!! views in the information schema often join many tables from the system catalogs to meet a strictly standardized format - why are slow
pg_catalog: Stores the metadata information about the database and cluster. Updated when ANALYZE or VACUM are performed. Catalogs in pg_catalog are typically prefixed with 'pg_' in example pg_database.
pg_toast: Hold TOAST storage for large tables.

User information is created in the global area of the database cluster and is common to all databases in the cluster. Settings, such as whether each user is allowed to connect and whether operations on individual objects are allowed, can be set on each database. However, CREATE USER is a user creation for a database cluster, not an operation on individual database.

List databases

select oid as database_id,
       datname as database_name,
       datallowconn as allow_connect,
       datconnlimit as connection_limit
from pg_database
order by oid;

Namespace

SELECT * FROM pg_catalog.pg_namespace ORDER BY nspname;

Database size

SELECT sum(pg_database_size(datname)) from pg_database;

List tables

SELECT * FROM pg_catalog.pg_tables;
SELECT 'alter table '||tablename||' rename to bak_'||tablename||';' FROM pg_catalog.pg_tables where schemaname = 'ebk' and tablename like 'imp%' order by tablename ;
SELECT 'drop table if exists '||tablename||' cascade;' FROM pg_catalog.pg_tables where schemaname = 'ebk' and tablename like 'bak_imp%' order by tablename ;
SELECT 'truncate table '||tablename||';' FROM pg_catalog.pg_tables where schemaname = 'aci_tmp_db' and tablename like 'e%' order by tablename ;

List tables without PK

select tab.table_schema,
       tab.table_name
from information_schema.tables tab
left join information_schema.table_constraints tco 
          on tab.table_schema = tco.table_schema
          and tab.table_name = tco.table_name 
          and tco.constraint_type = 'PRIMARY KEY'
where tab.table_type = 'BASE TABLE'
      and tab.table_schema not in ('pg_catalog', 'information_schema')
      and tco.constraint_name is null
order by table_schema,
         table_name;

Columny

SELECT *
  FROM information_schema.columns
 WHERE table_schema = 'your_schema'
   AND table_name   = 'your_table'
     ;

Information schema is slow

SELECT attrelid::regclass AS tbl
     , attname            AS col
     , atttypid::regtype  AS datatype
       -- more attributes?
FROM   pg_attribute
WHERE  attrelid = 'myschema.mytable'::regclass  -- table name optionally schema-qualified
AND    attnum > 0
AND    NOT attisdropped
ORDER  BY attnum;

SELECT json_object_keys(to_json(json_populate_record(NULL::schema_name.table_name, '{}'::JSON)))

Estimate rows in table

CREATE OR REPLACE FUNCTION estimated_row_count(text)
RETURNS bigint
LANGUAGE sql
AS $$
SELECT (CASE WHEN reltuples > 0 THEN
               pg_relation_size($1)*reltuples/(8192*relpages)
             ELSE 0
             END)::bigint
FROM pg_class
WHERE oid = $1::regclass;
$$;

select estimated_row_count('myschema.mytable');

SSL

select datname, usename, ssl, client_addr from pg_stat_ssl

List installed extensions

SELECT * FROM pg_extension;

List users

SELECT usename AS role_name,
  CASE 
     WHEN usesuper AND usecreatedb THEN 
	   CAST('superuser, create database' AS pg_catalog.text)
     WHEN usesuper THEN 
	    CAST('superuser' AS pg_catalog.text)
     WHEN usecreatedb THEN 
	    CAST('create database' AS pg_catalog.text)
     ELSE 
	    CAST('' AS pg_catalog.text)
  END role_attributes
FROM pg_catalog.pg_user
ORDER BY role_name desc;

List session

select pid as process_id, 
       usename as username, 
       datname as database_name, 
       client_addr as client_address, 
       application_name,
       backend_start,
       state,
       state_change
from pg_stat_activity;