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
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.
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:
You can see the full definition for any given index using the \d index-name meta-command: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)
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
Post a Comment