- 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)
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
|
No comments:
Post a Comment