- PostgreSQL Naming Rules
- Creating, Destroying, and Viewing Databases
- Creating New Tables
- Adding Indexes to a Table
- Getting Information About Databases and Tables
- Transaction Processing
- Summary
This chapter is from the book
This chapter is from the book
This chapter is from the book
Getting Information About Databases and Tables
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.
When you are using the psql client application, you can view the list of tables defined in your database using the \d meta-command:
movies=# \d
List of relations
Name | Type | Owner
------------------+-------+---------------
customers | table | bruce
distributors | table | bruce
rentals | table | bruce
returns | table | John Whorfin
tapes | table | bruce
To see the detailed definition of a particular table, use the \d table-name meta- command:
movies=# \d tapes
Table "tapes"
Column | Type | Modifiers
---------+-----------------------+-----------
tape_id | character(8) | not null
title | character varying(80) |
dist_id | integer |
Primary key: tapes_pkey
Triggers: RI_ConstraintTrigger_74939,
RI_ConstraintTrigger_74941,
RI_ConstraintTrigger_74953
You can also view a list of all indexes defined in your database. The \di meta-command displays indexes:
movies=# \di
List of relations
Name | Type | Owner
---------------------------+-------+---------------
customers_pkey | index | Administrator
distributors_pkey | index | Administrator
tapes_pkey | index | Administrator
You can see the full definition for any given index using the \d index-name meta-command:
movies=# \d tapes Index "tapes_pkey" Column | Type ---------+-------------- tape_id | character(8) unique btree (primary key)
Table 3.1 shows a complete list of the system catalog-related meta-commands in psql:
Table 3.1 System Catalog Meta-Commands
|
Command |
Result |
|
\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 |
|
\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 |
|
\l |
List all databases in this cluster |
Alternative Views (Oracle-Style Dictionary Views)
One of the nice things about an open-source product is that code contributions come from many different places. One such project exists to add Oracle-style dictionary views to PostgreSQL. If you are an experienced Oracle user, you will appreciate this feature. The orapgsqlviews project contributes Oracle-style views such as all_views, all_tables, user_tables, and so on. For more information, see http://gborg.postgresql.org.