- 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
rentals | table | bruce
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) | not null
duration | interval |
You can also view a list of all indexes defined in your database. The \di meta-command displays indexes:
movies=# \di
List of relations
Schema | Name | Type | Owner | Table
--------+---------------------------+-------+-------+-----------
public | customers_customer_id_key | index | korry | customers
You can see the full definition for any given index using the \d index-name meta-command:
movies=# \d customers_customer_id_key Index "public.customers_customer_id_key" Column | Type -------------+--------- customer_id | integer UNIQUE, btree, for table "public.customers"
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 |
|
\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 |
PostgreSQL version 8.0 introduced a set of views known as the INFORMATION_SCHEMA. The views defined in the INFORMATION_SCHEMA give you access to the information stored in the PostgreSQL system tables. The INFORMATION_SCHEMA is defined as part of the SQL standard and you’ll find an INFORMATION_SCHEMA in most commercial (and a few open-source) database systems. If you become familiar with the views defined in the INFORMATION_SCHEMA, you’ll find it much easier to move from one RDBMS system to another—every INFORMATION_SCHEMA contains the same set of views, each containing the same set of columns. For example, to see a list of the tables defined in your current database, you could execute the command:
SELECT table_schema, table_name, table_type FROM information_schema.tables;
You can execute that same query in DB2, MS SQL Server, or Informix (sadly, Oracle doesn’t support the INFORMATION_SCHEMA standard at the time we are writing this). So what can you find in the INFORMATION_SCHEMA?
schemata—Lists the schemas (in the current database) that are owned by you
tables—Lists all tables in the current database (actually, you only see those tables that you have the right to access in some way)
columns—Lists all columns in all tables that you have the right to access
views—Lists all of the views you have access to in the current database
table_privileges—Shows the privileges you hold (or that you granted) for each accessible object in the current database
domains—Lists all of the domains defined in the current database
check_constraints—Lists all of the CHECK constraints defined for the accessible tables (or domains) in the current database
There are more views in the INFORMATION_SCHEMA than we’ve described here (in fact, there are a total of 39 INFORMATION_SCHEMA views in PostgreSQL 8.0). See Chapter 30, "The Information Schema," of the PostgreSQL user guide for a complete list.
Why would you want to use the INFORMATION_SCHEMA instead of psql’s \d commands? We can think of three reasons. First, you can use the INFORMATION_SCHEMA inside of your own client applications—you can’t do that with the \d commands because they are part of the psql console application (itself a PostgreSQL client) instead of the PostgreSQL server. Second, by using the views defined in the INFORMATION_SCHEMA, you can read the PostgreSQL system tables using the same queries that you would use to read the DB2 system tables (or Sybase or SQL Server). That makes your client applications a bit more portable. Finally, you can write custom queries against the views defined in the INFORMATION_SCHEMA—you can’t customize the \d commands. For example, if you need to find all of the date columns in your database, just look inside of INFORMATION_SCHEMA.columns, like this:
SELECT DISTINCT table_name
FROM information_schema.columns WHERE data_type = ‘date’;
Need to know which columns can hold a NUMERIC value of at least seven digits? Use this query:
SELECT table_name,column_name, numeric_precision
FROM information_schema.columns
WHERE data_type = ‘numeric’ AND numeric_precision >= 7;
Of course, you can find all the information exposed by the INFORMATION_SCHEMA in the PostgreSQL system tables (pg_class, pg_index, and so on), but the INFORMATION_SCHEMA is often much easier to work with. The INFORMATION_SCHEMA views usually contain human-readable names for things like data type names, table names, and so on—the PostgreSQL system tables typically contain OIDs that you have to JOIN to another table in order to come up with a human-readable name.