# Databases : PostgreSQL : Introspection : Overview ## The Two Schemas The `information_schema` schema is defined and mandated by the SQL standard, and is composed mostly of views on `pg_catalog` relations. - The few tables are: `information_schema_catalog_name`, `sql_features`, `sql_implementation_info`, `sql_parts`, `sql_sizing` The `pg_catalog` schema contains the system tables and all the built-in data types, functions, and operators. - Since system table names begin with pg_, it is best to avoid such names to ensure that you won't suffer a conflict if some future version defines a system table named the same as your table. Both schemas automatically exist in all database, and `pg_catalog` is always part of the schema "search path" to ensure built-in names will always be findable. #### Special Data Types The `information_schema` uses special data types defined as simple domains over ordinary built-in types. - `cardinal_number` — A nonnegative integer. - `character_data` — A character string. - `sql_identifier` — A character string. Used for SQL identifiers. - `time_stamp` — A domain over the type timestamp with time zone. - `yes_or_no` — YES | NO (used to represent bools which were implemented after) ## Cheatsheet ###### List Connections ```sql SELECT datname, application_name, client_addr FROM pg_stat_activity where datname like 'p%'; ``` ###### List Databases ```sql SELECT datname FROM pg_database ORDER BY datname ``` ###### List Tables ```sql SELECT * FROM pg_catalog.pg_tables; SELECT table_name FROM information_schema.tables WHERE table_schema='public' AND table_type='BASE TABLE' ```