# Lesson 09: Data Organization / System Catalog > Native Markdown source page: Data Organization / System Catalog --- LLMS index: [llms.txt](/en/llms.txt) --- > [!NOTE] > This page is extracted from the official PDF into native Markdown. Slide text, code and speaker notes are kept in reading order; the original PDF and HTML remain the layout references. > **HTML source:** [dba1_09_data_catalog.html](https://edu.postgrespro.com/16/dba1-16-en/dba1_09_data_catalog.html) > **Archive:** [DBA1-handouts-16-en.zip](https://edu.postgrespro.com/16/DBA1-handouts-16-en.zip) > **PDF pages:** 18 ## Page 1: Data Organization / System Catalog {#source-page-001} ### Copyright © Postgres Professional, 2017–2025 Authors: Egor Rogov, Pavel Luzanov, Ilya Bashtanov, Aleksei Beresnev Translated by: Liudmila Mantrova, Alexander Meleshko, Elena Sharafutdinova Photo: Oleg Bartunov (Phu Monastery and Bhrikuti Peak, Nepal) ### Use of Course Materials Non-commercial use of course materials (presentations, demonstrations) is allowed without restrictions. Commercial use is possible only with the written permission of Postgres Professional. It is prohibited to make changes to the course materials. ### Feedback Please send your feedback, comments and suggestions to: edu@postgrespro.ru ### Disclaimer Postgres Professional assumes no responsibility for any damages and losses, including loss of income, caused by direct or indirect, intentional or accidental use of course materials. Postgres Professional company specifically disclaims any warranties on course materials. Course materials are provided “as is,” and Postgres Professional company has no obligations to provide maintenance, support, updates, enhancements, or modifications. ## Page 2: Topics {#source-page-002} - What Is the System Catalog and how to Access It - System Catalog Objects and their Location - Object Naming Rules - Special Data Types ## Page 3: System Catalog {#source-page-003} **A set of tables and views describing all objects in a database cluster** - Schemas - primary schema: pg\_catalog - alternative representation: information\_schema (SQL standard) - SQL access - view: SELECT - change: CREATE, ALTER, DROP - psql access - commands for convenient data visualization ### Speaker notes The system catalog is a collection of tables and views that describe all database objects. It is metadata for the contents of the cluster: Starting from version 14 of PostgreSQL, primary keys and unique constraints have been added for most system catalog tables. You can access this metadata using regular SQL queries. SELECT commands can give you a description of an object, and DDL (Data Definition Language) commands let you add and modify objects. All system catalog tables and views are located in the pg\_catalog schema. There is another schema, as defined by the SQL standard: information\_schema. It is more stable and portable than pg\_catalog, but does not reflect specific features of PostgreSQL. Client programs can read the contents of the system catalog and display it to the user in a convenient way. For example, GUI-based development and management environments usually come with a hierarchical object navigation tool. The psql client also offers a number of convenient built-in commands specifically designed for working with the system catalog. Most of these commands start with \\d (as in “describe”). For the full list of commands and their descriptions, see: MMANDS We will look at the most commonly used ones in the demo. The course materials also include the catalogs.pdf file that features a diagram of the main system catalog tables and related psql commands. ## Page 4: Cluster-Level Objects {#source-page-004} ![Source slide page 4: Cluster-Level Objects](images/page-004.png){#source-figure-004 caption="Source slide page 4: Cluster-Level Objects"} ### Speaker notes In a database cluster, each database has its own set of system catalog tables. However, there are several system catalog objects that are shared between all cluster databases. The most obvious example is the list of the databases themselves. These tables are stored outside of any single database, but at the same time they are accessible from any database within the cluster. ## Page 5: Naming Rules {#source-page-005} **Object (table, view) and column name prefixes** ```text pg_database.datname ``` - column prefix - (usually derived from - common prefix - for all objects - the object name) - Object names are always lowercase ### Speaker notes All system catalog tables and views begin with the prefix pg\_. In order to avoid potential conflicts, it is not recommended to create your own objects starting with pg\_. Column names have a three-letter prefix, which is usually derived from the name of the table. There is no underscore after the prefix. There are some exceptions to this rule, such as the oid column and others. Object names are always stored in lowercase. ## Page 6: Specific pg_catalog Objects {#source-page-006} Create a database and some test objects: ```sql => CREATE DATABASE data_catalog; ``` ```sql CREATE DATABASE ``` ```sql => \c data_catalog ``` ```text You are now connected to database "data_catalog" as user "student". ``` ```sql => CREATE TABLE employees( id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY, name text, manager integer ); ``` ```sql CREATE TABLE ``` ```sql => CREATE VIEW top_managers AS SELECT * FROM employees WHERE manager IS NULL; ``` ```sql CREATE VIEW ``` ```sql => CREATE TEMP TABLE emp_salaries( employee integer, salary numeric ); ``` ```sql CREATE TABLE ``` We are familiar with some of the system catalog tables from the previous lesson. This is databases: ```sql => SELECT * FROM pg_database WHERE datname = 'data_catalog' \gx ``` ```text -[ RECORD 1 ]--+------------- oid | 16386 datname | data_catalog datdba | 16384 encoding | 6 datlocprovider | c datistemplate | f datallowconn | t datconnlimit | -1 datfrozenxid | 722 datminmxid | 1 dattablespace | 1663 datcollate | en_US.UTF-8 datctype | en_US.UTF-8 daticulocale | daticurules | datcollversion | 2.39 datacl | ``` And schemas: ```sql => SELECT * FROM pg_namespace WHERE nspname = 'public' \gx ``` ```text -[ RECORD 1 ]----------------------------------------------------------- oid | 2200 nspname | public nspowner | 6171 nspacl | {pg_database_owner=UC/pg_database_owner,=U/pg_database_owner} ``` pg\_class is an important table that stores descriptions for multiple types of objects: tables, views, indexes, sequences. All these objects in PostgreSQL are called relations, thus the prefix “rel” in the column names: ```sql => SELECT relname, relkind, relnamespace, relfilenode, relowner, relpersistence FROM pg_class WHERE relname ~ '^(emp|top)'; ``` ## Page 7: Page 7 {#source-page-007} ```text relname | relkind | relnamespace | relfilenode | relowner | relpersistence ------------------+---------+--------------+-------------+----------+---------------- employees_id_seq | S | 2200 | 16387 | 16384 | p employees | r | 2200 | 16388 | 16384 | p employees_pkey | i | 2200 | 16393 | 16384 | p top_managers | v | 2200 | 0 | 16384 | p emp_salaries | r | 16399 | 16401 | 16384 | t (5 rows) ``` The object type is determined by the relkind column, while relpersistence distinguishes temporary objects from permanent ones. When actively using temporary objects, the system catalog tables accumulate numerous obsolete row versions, which may degrade query performance at all query processing stages. In such cases, timely catalog table vacuuming becomes essential. Naturally, only a subset of columns in pg\_class are relevant for each object type. Moreover, examining object names (rather than identifiers like relnamespace, relowner, etc.) is more practical. For this purpose, there are specialized system views such as: ```sql => SELECT schemaname, tablename, tableowner FROM pg_tables WHERE schemaname ~ '(public|pg_temp.+)'; ``` ```text schemaname | tablename | tableowner ------------+--------------+------------ public | employees | student pg_temp_4 | emp_salaries | student (2 rows) ``` ```sql => SELECT * FROM pg_views WHERE schemaname = 'public'; ``` ```sql schemaname | viewname | viewowner | definition ------------+--------------+-----------+---------------------------- public | top_managers | student | SELECT id, + | | | name, + | | | manager + | | | FROM employees + | | | WHERE (manager IS NULL); (1 row) ``` ### Using psql psql has a set of built-in commands for obtaining information from the system catalog. These short commands are more convenient than making direct queries to system tables and views. A list of all tables is obtained with the command: ```sql => \dt ``` ```text List of relations Schema | Name | Type | Owner -----------+--------------+-------+--------- pg_temp_4 | emp_salaries | table | student public | employees | table | student (2 rows) ``` This command returns a list of all views in the public schema: ```text => \dv public.* ``` ```text List of relations Schema | Name | Type | Owner --------+--------------+------+--------- public | top_managers | view | student (1 row) ``` List of tables, views, indexes, and sequences: ```text => \dtvis ``` ## Page 8: Page 8 {#source-page-008} ```text List of relations Schema | Name | Type | Owner | Table -----------+------------------+----------+---------+----------- pg_temp_4 | emp_salaries | table | student | public | employees | table | student | public | employees_id_seq | sequence | student | public | employees_pkey | index | student | employees public | top_managers | view | student | (5 rows) ``` Appended with the + modifier, these commands will return more detailed data: ```sql => \dt+ ``` ```text List of relations Schema | Name | Type | Owner | Persistence | Access method | Size | Description -----------+--------------+-------+---------+-------------+---------------+------------+-- ----------- pg_temp_4 | emp_salaries | table | student | temporary | heap | 8192 bytes | public | employees | table | student | permanent | heap | 8192 bytes | (2 rows) ``` To get detailed information about a specific object, use the \\d command (without any additional letters): ```sql => \d top_managers ``` ```text View "public.top_managers" Column | Type | Collation | Nullable | Default ---------+---------+-----------+----------+--------- id | integer | | | name | text | | | manager | integer | | | ``` The + modifier still works: ```sql => \d+ top_managers ``` ```sql View "public.top_managers" Column | Type | Collation | Nullable | Default | Storage | Description ---------+---------+-----------+----------+---------+----------+------------- id | integer | | | | plain | name | text | | | | extended | manager | integer | | | | plain | View definition: SELECT id, name, manager FROM employees WHERE manager IS NULL; ``` You can use the command not only on relations, but other objects as well, such as schemas (\\dn) and functions (\\df). The S modifier makes the command display system objects in addition to user ones. You can use wildcard patterns to filter the output: ```text => \dfS pg*size ``` ```text List of functions Schema | Name | Result data type | Argument data types | Type ------------+------------------------+------------------+---------------------+------ pg_catalog | pg_column_size | integer | "any" | func pg_catalog | pg_database_size | bigint | name | func pg_catalog | pg_database_size | bigint | oid | func pg_catalog | pg_indexes_size | bigint | regclass | func pg_catalog | pg_relation_size | bigint | regclass | func pg_catalog | pg_relation_size | bigint | regclass, text | func pg_catalog | pg_table_size | bigint | regclass | func pg_catalog | pg_tablespace_size | bigint | name | func pg_catalog | pg_tablespace_size | bigint | oid | func pg_catalog | pg_total_relation_size | bigint | regclass | func (10 rows) ``` Usually, such psql commands have mnemonic names. For example, \\df is describe function, \\sf is show function: ## Page 9: Exploring the System Catalog Structure {#source-page-009} ```text => \sf pg_catalog.pg_database_size(oid) ``` ```sql CREATE OR REPLACE FUNCTION pg_catalog.pg_database_size(oid) RETURNS bigint LANGUAGE internal PARALLEL SAFE STRICT AS $function$pg_database_size_oid$function$ ``` You can get the full list of commands from the documentation or with the psql \\? command. All psql commands that describe objects query system catalog tables. To view these queries, set the psql variable ECHO\_HIDDEN. For example, to examine the employees table structure: ```text => \set ECHO_HIDDEN on ``` ```sql => \dt employees ``` ```sql ********* QUERY ********** SELECT n.nspname as "Schema", c.relname as "Name", CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm' THEN 'materialized view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 't' THEN 'TOAST table' WHEN 'f' THEN 'foreign table' WHEN 'p' THEN 'partitioned table' WHEN 'I' THEN 'partitioned index' END as "Type", pg_catalog.pg_get_userbyid(c.relowner) as "Owner" FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace LEFT JOIN pg_catalog.pg_am am ON am.oid = c.relam WHERE c.relkind IN ('r','p','t','s','') AND c.relname OPERATOR(pg_catalog.~) '^(employees)$' COLLATE pg_catalog.default AND pg_catalog.pg_table_is_visible(c.oid) ORDER BY 1,2; ************************** ``` ```text List of relations Schema | Name | Type | Owner --------+-----------+-------+--------- public | employees | table | student (1 row) ``` ```text => \unset ECHO_HIDDEN ``` Most system catalog tables feature primary keys (typically the oid column) and uniqueness constraints. As an example, the pg\_attribute table (containing relation attribute information) implements these constraints: ```sql => \d pg_attribute ``` ## Page 10: Page 10 {#source-page-010} ```text Table "pg_catalog.pg_attribute" Column | Type | Collation | Nullable | Default ----------------+-----------+-----------+----------+--------- attrelid | oid | | not null | attname | name | | not null | atttypid | oid | | not null | attlen | smallint | | not null | attnum | smallint | | not null | attcacheoff | integer | | not null | atttypmod | integer | | not null | attndims | smallint | | not null | attbyval | boolean | | not null | attalign | "char" | | not null | attstorage | "char" | | not null | attcompression | "char" | | not null | attnotnull | boolean | | not null | atthasdef | boolean | | not null | atthasmissing | boolean | | not null | attidentity | "char" | | not null | attgenerated | "char" | | not null | attisdropped | boolean | | not null | attislocal | boolean | | not null | attinhcount | smallint | | not null | attstattarget | smallint | | not null | attcollation | oid | | not null | attacl | aclitem[] | | | attoptions | text[] | C | | attfdwoptions | text[] | C | | attmissingval | anyarray | | | Indexes: "pg_attribute_relid_attnum_index" PRIMARY KEY, btree (attrelid, attnum) "pg_attribute_relid_attnam_index" UNIQUE CONSTRAINT, btree (attrelid, attname) ``` Referential integrity is maintained through foreign-key-like constraints with additional complexity: the referencing column may be an array of elements, zero may represent undefined references. The pg\_get\_catalog\_foreign\_keys() function lists these pseudo-foreign keys. For instance, pg\_attribute references: ```sql => SELECT * FROM pg_get_catalog_foreign_keys() WHERE fktable = 'pg_attribute'::regclass; ``` ```text fktable | fkcols | pktable | pkcols | is_array | is_opt --------------+----------------+--------------+--------+----------+-------- pg_attribute | {attrelid} | pg_class | {oid} | f | f pg_attribute | {atttypid} | pg_type | {oid} | f | t pg_attribute | {attcollation} | pg_collation | {oid} | f | t (3 rows) ``` fktable, fkcols — referencing table and its columns pktable, pkcols — key referenced is\_array — whether the referencing column is an array is\_opt — whether the referencing column can contain 0 ## Page 11: Special Data Types {#source-page-011} **oid type – object identifier** - oid column ensures object uniqueness in system catalog tables - integer with an auto increment - reg\* types - oid aliases for some system catalog tables - (regclass for pg\_class, etc.) - converting the text name of an object to the oid type and vice versa ### Speaker notes Most system catalog tables use a column with oid name and data type of the same name as a primary key. The oid (Object Identifier) type is a 32 bit integer (about 4 billion possible values) with an auto increment. There are several special data types (in fact, oid aliases) starting with reg that are used to convert object names to oids and back. ## Page 12: oid and reg* Data Types {#source-page-012} As shown before, table and view descriptions are stored in pg\_class table, and column descriptions are in a separate pg\_attribute table. So, to get a list of columns in a specific table, you need to join pg\_class and pg\_attribute: ```sql => SELECT a.attname, a.atttypid FROM pg_attribute a WHERE a.attrelid = ( SELECT oid FROM pg_class WHERE relname = 'employees' ) AND a.attnum > 0; ``` ```text attname | atttypid ---------+---------- id | 23 name | 25 manager | 23 (3 rows) ``` Using reg\* types, the query can be simplified by omitting the explicit access to pg\_class: ```sql => SELECT a.attname, a.atttypid FROM pg_attribute a WHERE a.attrelid = 'employees'::regclass AND a.attnum > 0; ``` ```text attname | atttypid ---------+---------- id | 23 name | 25 manager | 23 (3 rows) ``` Here, the string “employees” was converted to the oid type. Similarly, oid can be displayed as a text value: ```sql => SELECT a.attname, a.atttypid::regtype FROM pg_attribute a WHERE a.attrelid = 'employees'::regclass AND a.attnum > 0; ``` ```text attname | atttypid ---------+---------- id | integer name | text manager | integer (3 rows) ``` A list of all reg\* types: ```sql => \dT reg* ``` ```text List of data types Schema | Name | Description ------------+---------------+-------------------------------------- pg_catalog | regclass | registered class pg_catalog | regcollation | registered collation pg_catalog | regconfig | registered text search configuration pg_catalog | regdictionary | registered text search dictionary pg_catalog | regnamespace | registered namespace pg_catalog | regoper | registered operator pg_catalog | regoperator | registered operator (with args) pg_catalog | regproc | registered procedure pg_catalog | regprocedure | registered procedure (with args) pg_catalog | regrole | registered role pg_catalog | regtype | registered type (11 rows) ``` ## Page 13: Takeaways {#source-page-013} **The system catalog contains cluster metadata stored within the cluster itself** - SQL access and additional psql commands - Some system catalog tables are stored in databases, some are shared across the entire cluster - The system catalog uses special data types ## Page 14: Practice {#source-page-014} 1. Get a description of the pg\_class table. - 2. Get a detailed description of the pg\_tables view. - 3. Create a database and a temporary table in it. Get a complete list of schemas in the database, including system schemas. - 4. Get a list of views in the information\_schema. - 5. What queries does the following psql command perform? \\d+ pg\_views ## Page 15: 1. Description of pg_class {#source-page-015} ```sql => \d pg_class ``` ```text Table "pg_catalog.pg_class" Column | Type | Collation | Nullable | Default ---------------------+--------------+-----------+----------+--------- oid | oid | | not null | relname | name | | not null | relnamespace | oid | | not null | reltype | oid | | not null | reloftype | oid | | not null | relowner | oid | | not null | relam | oid | | not null | relfilenode | oid | | not null | reltablespace | oid | | not null | relpages | integer | | not null | reltuples | real | | not null | relallvisible | integer | | not null | reltoastrelid | oid | | not null | relhasindex | boolean | | not null | relisshared | boolean | | not null | relpersistence | "char" | | not null | relkind | "char" | | not null | relnatts | smallint | | not null | relchecks | smallint | | not null | relhasrules | boolean | | not null | relhastriggers | boolean | | not null | relhassubclass | boolean | | not null | relrowsecurity | boolean | | not null | relforcerowsecurity | boolean | | not null | relispopulated | boolean | | not null | relreplident | "char" | | not null | relispartition | boolean | | not null | relrewrite | oid | | not null | relfrozenxid | xid | | not null | relminmxid | xid | | not null | relacl | aclitem[] | | | reloptions | text[] | C | | relpartbound | pg_node_tree | C | | Indexes: "pg_class_oid_index" PRIMARY KEY, btree (oid) "pg_class_relname_nsp_index" UNIQUE CONSTRAINT, btree (relname, relnamespace) "pg_class_tblspc_relfilenode_index" btree (reltablespace, relfilenode) ``` ### 2. Detailed Description of pg\\\_tables ```sql => \d+ pg_tables ``` ```sql View "pg_catalog.pg_tables" Column | Type | Collation | Nullable | Default | Storage | Description -------------+---------+-----------+----------+---------+---------+------------- schemaname | name | | | | plain | tablename | name | | | | plain | tableowner | name | | | | plain | tablespace | name | | | | plain | hasindexes | boolean | | | | plain | hasrules | boolean | | | | plain | hastriggers | boolean | | | | plain | rowsecurity | boolean | | | | plain | View definition: SELECT n.nspname AS schemaname, c.relname AS tablename, pg_get_userbyid(c.relowner) AS tableowner, t.spcname AS tablespace, c.relhasindex AS hasindexes, c.relhasrules AS hasrules, c.relhastriggers AS hastriggers, c.relrowsecurity AS rowsecurity FROM pg_class c LEFT JOIN pg_namespace n ON n.oid = c.relnamespace LEFT JOIN pg_tablespace t ON t.oid = c.reltablespace WHERE c.relkind = ANY (ARRAY['r'::"char", 'p'::"char"]); ``` ## Page 16: 3. List of All Schemas {#source-page-016} ```sql => CREATE DATABASE data_catalog; ``` ```sql CREATE DATABASE ``` ```sql => \c data_catalog ``` ```text You are now connected to database "data_catalog" as user "student". ``` ```sql => CREATE TEMP TABLE t(n integer); ``` ```sql CREATE TABLE ``` ```text => \dnS ``` ```text List of schemas Name | Owner --------------------+------------------- information_schema | postgres pg_catalog | postgres pg_temp_4 | postgres pg_toast | postgres pg_toast_temp_4 | postgres public | pg_database_owner (6 rows) ``` Temporary tables are stored in schemas named pg\_temp\_N, where N is a number. Such schemas are created for each session where temporary objects appear, so there can be multiple schemas. To get the name of the schema for the current session, use the following system function: ```sql => SELECT pg_my_temp_schema()::regnamespace; ``` ```text pg_my_temp_schema ------------------- pg_temp_4 (1 row) ``` In general, the exact name of the schema is not required: you can access temporary objects in your session by using just pg\_temp: ```sql => SELECT * FROM pg_temp.t; ``` ```text n --- (0 rows) ``` We already know what some of the schemas are there for, and we will learn more about the rest (pg\_toast\*) in a later lesson. ### 4. Get a List of Views in the information\\\_schema Use the template: ```text => \dv information_schema.* ``` ## Page 17: Page 17 {#source-page-017} ```text List of relations Schema | Name | Type | Owner --------------------+---------------------------------------+------+---------- information_schema | _pg_foreign_data_wrappers | view | postgres information_schema | _pg_foreign_servers | view | postgres information_schema | _pg_foreign_table_columns | view | postgres information_schema | _pg_foreign_tables | view | postgres information_schema | _pg_user_mappings | view | postgres information_schema | administrable_role_authorizations | view | postgres information_schema | applicable_roles | view | postgres information_schema | attributes | view | postgres information_schema | character_sets | view | postgres information_schema | check_constraint_routine_usage | view | postgres information_schema | check_constraints | view | postgres information_schema | collation_character_set_applicability | view | postgres information_schema | collations | view | postgres information_schema | column_column_usage | view | postgres information_schema | column_domain_usage | view | postgres information_schema | column_options | view | postgres information_schema | column_privileges | view | postgres information_schema | column_udt_usage | view | postgres information_schema | columns | view | postgres information_schema | constraint_column_usage | view | postgres information_schema | constraint_table_usage | view | postgres information_schema | data_type_privileges | view | postgres information_schema | domain_constraints | view | postgres information_schema | domain_udt_usage | view | postgres information_schema | domains | view | postgres information_schema | element_types | view | postgres information_schema | enabled_roles | view | postgres information_schema | foreign_data_wrapper_options | view | postgres information_schema | foreign_data_wrappers | view | postgres information_schema | foreign_server_options | view | postgres information_schema | foreign_servers | view | postgres information_schema | foreign_table_options | view | postgres information_schema | foreign_tables | view | postgres information_schema | information_schema_catalog_name | view | postgres information_schema | key_column_usage | view | postgres information_schema | parameters | view | postgres information_schema | referential_constraints | view | postgres information_schema | role_column_grants | view | postgres information_schema | role_routine_grants | view | postgres information_schema | role_table_grants | view | postgres information_schema | role_udt_grants | view | postgres information_schema | role_usage_grants | view | postgres information_schema | routine_column_usage | view | postgres information_schema | routine_privileges | view | postgres information_schema | routine_routine_usage | view | postgres information_schema | routine_sequence_usage | view | postgres information_schema | routine_table_usage | view | postgres information_schema | routines | view | postgres information_schema | schemata | view | postgres information_schema | sequences | view | postgres information_schema | table_constraints | view | postgres information_schema | table_privileges | view | postgres information_schema | tables | view | postgres information_schema | transforms | view | postgres information_schema | triggered_update_columns | view | postgres information_schema | triggers | view | postgres information_schema | udt_privileges | view | postgres information_schema | usage_privileges | view | postgres information_schema | user_defined_types | view | postgres information_schema | user_mapping_options | view | postgres information_schema | user_mappings | view | postgres information_schema | view_column_usage | view | postgres information_schema | view_routine_usage | view | postgres information_schema | view_table_usage | view | postgres information_schema | views | view | postgres (65 rows) ``` ### 5. Queries to the System Catalog To see the queries psql runs, use the ECHO\_HIDDEN variable: ```text => \set ECHO_HIDDEN on ``` ```sql => \d+ pg_views ``` ## Page 18: Page 18 {#source-page-018} ```sql ********* QUERY ********** SELECT c.oid, n.nspname, c.relname FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE c.relname OPERATOR(pg_catalog.~) '^(pg_views)$' COLLATE pg_catalog.default AND pg_catalog.pg_table_is_visible(c.oid) ORDER BY 2, 3; ************************** ``` ```sql ********* QUERY ********** SELECT c.relchecks, c.relkind, c.relhasindex, c.relhasrules, c.relhastriggers, c.relrowsecurity, c.relforcerowsecurity, false AS relhasoids, c.relispartition, pg_catalog.array_to_string(c.reloptions || array(select 'toast.' || x from pg_catalog.unnest(tc.reloptions) x), ', ') , c.reltablespace, CASE WHEN c.reloftype = 0 THEN '' ELSE c.reloftype::pg_catalog.regtype::pg_catalog.text END, c.relpersistence, c.relreplident, am.amname FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_class tc ON (c.reltoastrelid = tc.oid) LEFT JOIN pg_catalog.pg_am am ON (c.relam = am.oid) WHERE c.oid = '12028'; ************************** ``` ```sql ********* QUERY ********** SELECT a.attname, pg_catalog.format_type(a.atttypid, a.atttypmod), (SELECT pg_catalog.pg_get_expr(d.adbin, d.adrelid, true) FROM pg_catalog.pg_attrdef d WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef), a.attnotnull, (SELECT c.collname FROM pg_catalog.pg_collation c, pg_catalog.pg_type t WHERE c.oid = a.attcollation AND t.oid = a.atttypid AND a.attcollation <> t.typcollation) AS attcollation, a.attidentity, a.attgenerated, a.attstorage, pg_catalog.col_description(a.attrelid, a.attnum) FROM pg_catalog.pg_attribute a WHERE a.attrelid = '12028' AND a.attnum > 0 AND NOT a.attisdropped ORDER BY a.attnum; ************************** ``` ```sql ********* QUERY ********** SELECT pg_catalog.pg_get_viewdef('12028'::pg_catalog.oid, true); ************************** ``` ```sql ********* QUERY ********** SELECT r.rulename, trim(trailing ';' from pg_catalog.pg_get_ruledef(r.oid, true)) FROM pg_catalog.pg_rewrite r WHERE r.ev_class = '12028' AND r.rulename != '_RETURN' ORDER BY 1; ************************** ``` ```sql View "pg_catalog.pg_views" Column | Type | Collation | Nullable | Default | Storage | Description ------------+------+-----------+----------+---------+----------+------------- schemaname | name | | | | plain | viewname | name | | | | plain | viewowner | name | | | | plain | definition | text | | | | extended | View definition: SELECT n.nspname AS schemaname, c.relname AS viewname, pg_get_userbyid(c.relowner) AS viewowner, pg_get_viewdef(c.oid) AS definition FROM pg_class c LEFT JOIN pg_namespace n ON n.oid = c.relnamespace WHERE c.relkind = 'v'::"char"; ``` psql ran five queries to display this result. ```text => \set ECHO_HIDDEN off ```