Viewing Table Descriptions

At this point, you've defined three tables in the movies database: tapes, customers, and rentals. If you want to view the table definitions, you can use the d meta-command in psql (remember that a meta-command is not really a SQL command, but a command understood by the psql client). The d meta-command comes in two flavors: If you include a table name (d customers), you will see the definition of that table; if you don't include a table name, d will show you a list of all the tables defined in your database.

$ psql -d movies
Welcome to psql, the PostgreSQL interactive terminal.

Type: copyright for distribution terms
 h for help with SQL commands
 ? for help on internal slash commands
 g or terminate with semicolon to execute query
 q to quit

movies=# d
 List of relations
 Name | Type | Owner
 customers | table | bruce
 rentals | table | bruce
 tapes | table | bruce
(3 rows)

movies=# d tapes
 Table "tapes"

 Column | Type | Modifiers
 tape_id | character(8) |
 title | character varying(80) |
 duration | interval |

 "tapes_tape_id_key" UNIQUE, btree (tape_id)

movies=# d customers
 Table "customers"
 Attribute | Type | Modifier
 customer_id | integer |
 customer_name| character varying(50) |
 phone | character(8) |
 birth_date | date |
 balance | numeric(7,2) |
Index: customers_customer_id_key

movies=# d rentals
 Table "rentals"
 Attribute | Type | Modifier
 tape_id | character(8) |
 customer_id | integer |
 rental_date | date |


I'll point out a few things about the d meta-command.

Notice that for each column in a table, the d meta-command returns three pieces of information: the column name (or Attribute), the data type, and a Modifier.

The data type reported by the d meta-command is spelled out; you won't see char(n) or varchar(n), you'll see character(n) and character varying(n) instead.

The Modifier column shows additional column attributes. The most commonly encountered modifiers are NOT NULL and DEFAULT .... The NOT NULL modifier appears when you create a mandatory columnmandatory means that each row in the table must have a value for that column. The DEFAULT ... modifier appears when you create a column with a default value. A default value is inserted into a column when you don't specify a value for a column. If you don't specify a default value, PostgreSQL inserts the special value NULL. I'll discuss NULL values and default values in more detail in Chapter 2.

You might have noticed that the listing for the tapes and customers tables show that an index has been created. PostgreSQL automatically creates an index for you when you define UNIQUE columns. An index is a data structure that PostgreSQL can use to ensure uniqueness. Indexes are also used to increase performance. I'll cover indexes in more detail in Chapter 3, "PostgreSQL SQL Syntax and Use."

Depending on which version of PostgreSQL you're using, you may see each table name listed as "Table "public.table-name". The "public" part is the name of the schema that the table is defined in.

Part I: General PostgreSQL Use

Introduction to PostgreSQL and SQL

Working with Data in PostgreSQL

PostgreSQL SQL Syntax and Use


Part II: Programming with PostgreSQL

Introduction to PostgreSQL Programming

Extending PostgreSQL


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


Replicating PostgreSQL Data with Slony

Contributed Modules


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
Year: 2004
Pages: 261 © 2008-2020.
If you may any questions please contact us: