Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

PostgreSQL System Catalog Views

  • Catalogs are named collections of schemas in an SQL-environment
  • When you create a table, PostgreSQL stores the definition of that table in the system catalog. The system catalog is a collection of PostgreSQL tables. You can issue SELECT statements against the system catalog tables just like any other table, but there are easier ways to view table and index definitions.
  • A cluster is an implementation-defined collection of catalogs.
  • Exactly one cluster is associated with an SQL-session
Different between catalog and schema:
Cluster > Catalog > Schema > Table > Columns & Rows
So in both Postgres and the SQL Standard we have this containment hierarchy:

  • A computer may have one cluster or multiple.
  • A database server is a cluster.
  • A cluster has catalogs. ( Catalog = Database )
  • Catalogs have schemas. (Schema = namespace of tables, and security boundary)
  • Schemas have tables.
  • Tables have rows.
  • Rows have values, defined by columns.
When you are using the psql client application, you can view the list of tables defined in your database using the \d meta-command ,\dS+ means more info: 
postgres=# \d
                List of relations
 Schema |       Name        |   Type   |  Owner   
--------+-------------------+----------+----------
 public | batch             | table    | postgres
 public | cellphones        | table    | postgres
 public | cellphones_id_seq | sequence | postgres
 public | distributorc      | table    | u8
postgres=# \dS+
                                       List of relations
   Schema   |              Name               |   Type   |  Owner   |    Size    | Description 
------------+---------------------------------+----------+----------+------------+-------------
 pg_catalog | pg_views                        | view     | postgres | 0 bytes    | 
 public     | batch                           | table    | postgres | 8192 bytes | 
 public     | cellphones                      | table    | postgres | 0 bytes    | 
 public     | cellphones_id_seq               | sequence | postgres | 8192 bytes | 
 public     | distributorc                    | table    | u8       | 0 bytes    | 
 public     | distributors                    | table    | u8       | 0 bytes    | 
 public     | distributorsct                  | table    | u8       | 0 bytes    | 


To see the detailed definition of a particular table, use the \d table-name meta-command:
postgres=# \d batch
          Table "public.batch"
   Column   |     Type      | Modifiers 
------------+---------------+-----------
 batch_id   | integer       | 
 batch_name | character(35) | 
 members    | integer       | 
Indexes:
    "mul_uni_key" UNIQUE CONSTRAINT, btree (batch_id, members)
You can also view a list of all indexes defined in your database. The \di meta-command displays indexes:
postgres=# \di
                         List of relations
 Schema |          Name           | Type  |  Owner   |    Table     
--------+-------------------------+-------+----------+--------------
 public | cellphones_pkey         | index | postgres | cellphones
 public | code_title              | index | u8       | primtab
 public | distributors_pkey       | index | u8       | distributors
 public | mobiles_pkey            | index | postgres | mobiles
 public | mul_uni_key             | index | postgres | batch
postgres=# \diS+
                                                      List of relations
   Schema   |                  Name                   | Type  |  Owner   |          Table          |    Size    | Description 
------------+-----------------------------------------+-------+----------+-------------------------+------------+-------------
 pg_catalog | pg_user_mapping_user_server_index       | index | postgres | pg_user_mapping         | 8192 bytes | 
 .
 .
 .
 public     | cellphones_pkey                         | index | postgres | cellphones              | 8192 bytes | 
 public     | code_title                              | index | u8       | primtab                 | 8192 bytes | 
 public     | distributors_pkey                       | index | u8       | distributors            | 8192 bytes | 
 public     | mobiles_pkey                            | index | postgres | mobiles                 | 16 kB      | 
 public     | mul_uni_key                             | index | postgres | batch                   | 16 kB      | 
 public     | pkey                                    | index | postgres | postgres3               | 16 kB      | 
 public     | postgres1_order_no_key                  | index | postgres | postgres1               | 16 kB      | 
 public     | postgres_ord_no_key                     | index | postgres | postgres                | 8192 bytes | 
 public     | prikey                                  | index | u8       | films                   | 8192 bytes | 
 public     | production                              | index | u8       | k                       | 8192 bytes | 
 public     | students_student_id_key                 | index | postgres | students                | 16 kB      | 
 public     | uni_constraint                          | index | postgres | cellphones              | 8192 bytes | 
 public     | uni_constraint2                         | index | postgres | mobiles                 | 16 kB      | 
 public     | uni_index                               | index | postgres | cellphones              | 8192 bytes | 
 public     | uni_key                                 | index | postgres | india                   | 8192 bytes | 
(110 rows)
You can see the full definition for any given index using the \d index-name meta-command:
postgres=# \diS+ uni_key
                           List of relations
 Schema |  Name   | Type  |  Owner   | Table |    Size    | Description 
--------+---------+-------+----------+-------+------------+-------------
 public | uni_key | index | postgres | india | 8192 bytes | 
(1 row)
System Catalog Meta-Commands
Command
Result
\dd object-name
Display comments for object-name
\db
List all tablespaces
\dn
List all schemas
\d_\dt
List all tables
\di
List all indexes
\ds
List all sequences
\dv
List all views
\dS
List all PostgreSQL-defined tables
\d table-name
Show table definition
\d index-name
Show index definition
\d view-name
Show view definition
\d sequence-name
Show sequence definition
\dp
List all privileges
\dl
List all large objects
\da
List all aggregates
\df
List all functions
\dc
List all conversions
\dC
List all casts
\df function-name
List all functions with given name
\do
List all operators
\do operator-name
List all operators with given name
\dT
List all types
\dD
List all domains
\dg
List all groups
\du
List all users
\l
List all databases in this cluster


Comments

Popular posts from this blog

PostgreSQL Pgbadger Installation On Linux

PostgreSQL pgBadger

How to configure Replication Manager (repmgr) ?

Postgresql Database Tutorial android app

7 Steps to configure BDR replication in postgresql