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

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.

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 anotherevery 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_SCHEMAinstead of psql's d commands? We can think of three reasons. First, you can use the INFORMATION_SCHEMA inside of your own client applicationsyou 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_SCHEMAyou 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 onthe PostgreSQL system tables typically contain OIDs that you have to JOIN to another table in order to come up with a human-readable name.

Part I: General PostgreSQL Use

Introduction to PostgreSQL and SQL

Working with Data in PostgreSQL

PostgreSQL SQL Syntax and Use

Performance

Part II: Programming with PostgreSQL

Introduction to PostgreSQL Programming

Extending PostgreSQL

PL/pgSQL

The PostgreSQL C APIlibpq

A Simpler C APIlibpgeasy

The New PostgreSQL C++ APIlibpqxx

Embedding SQL Commands in C Programsecpg

Using PostgreSQL from an ODBC Client Application

Using PostgreSQL from a Java Client Application

Using PostgreSQL with Perl

Using PostgreSQL with PHP

Using PostgreSQL with Tcl and Tcl/Tk

Using PostgreSQL with Python

Npgsql: The .NET Data Provider

Other Useful Programming Tools

Part III: PostgreSQL Administration

Introduction to PostgreSQL Administration

PostgreSQL Administration

Internationalization and Localization

Security

Replicating PostgreSQL Data with Slony

Contributed Modules

Index



PostgreSQL(c) The comprehensive guide to building, programming, and administering PostgreSQL databases
PostgreSQL(c) The comprehensive guide to building, programming, and administering PostgreSQL databases
ISBN: 735712573
EAN: N/A
Year: 2004
Pages: 261

Flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net