7.2 Building Tables

The core components of all relational databases are tables. A table consists of columns, which have certain data types. In this section you will learn how to create simple tables.

To create tables, the CREATE TABLE command must be used. CREATE TABLE is one of the most flexible and most powerful commands available. If you take a look at the syntax overview of the command, you will see that it provides many settings and attributes that can be assigned to a table:

 phpbook=# \h CREATE TABLE Command:     CREATE TABLE Description: define a new table Syntax: CREATE [ [ LOCAL ] { TEMPORARY | TEMP } ] TABLE table_name (     { column_name data_type [ DEFAULT default_expr ] [ column_constraint [, ... ] ]     | table_constraint }  [, ... ] ) [ INHERITS ( parent_table [, ... ] ) ] [ WITH OIDS | WITHOUT OIDS ] where column_constraint is: [ CONSTRAINT constraint_name ] { NOT NULL | NULL | UNIQUE | PRIMARY KEY |   CHECK (expression) |   REFERENCES reftable [ ( refcolumn ) ] [ MATCH FULL | MATCH PARTIAL ]     [ ON DELETE action ] [ ON UPDATE action ] } [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ] and table_constraint is: [ CONSTRAINT constraint_name ] { UNIQUE ( column_name [, ... ] ) |   PRIMARY KEY ( column_name [, ... ] ) |   CHECK ( expression ) |   FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) ]     [ MATCH FULL | MATCH PARTIAL ] [ ON DELETE action ] [ ON UPDATE action ] } [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ] 

As you can see, CREATE TABLE provides many constraints that you will need to build sophisticated data structures. Detailed coverage of constraints will be provided in Chapter 8, "Advanced SQL."

Let's create a table now:

 phpbook=# CREATE TABLE t_person(id int4, name varchar(10)); CREATE 

The preceding code generates a table containing two columns. The first column is called id and can contain 4-byte integers. The second column is called name and can contain strings of variable length that can be up to 10 characters long. Take a look at the data structure:

 phpbook=# \d t_person               Table "t_person"  Column |         Type          | Modifiers --------+-----------------------+-----------  id     | integer               |  name   | character varying(10) | 

With the help of \d, it is easy to find out about the data structure of the table you have just created.

To remove the table from the system, SQL provides a command called DROP TABLE:

 phpbook=# \h DROP TABLE Command:     DROP TABLE Description: remove a table Syntax: DROP TABLE name [, ...] 

As you can see, the syntax of the command is easy. Simply pass the name of the table you want to drop to the command and press Enter:

 phpbook=# DROP TABLE t_person; DROP 

If no error occurred, the table has now been successfully removed from the database.

If more than one table has to be created, it can also be uncomfortable to use the interactive shell. Therefore PostgreSQL provides an additional way of communicating with the user.

Let's write an ASCII file containing the code you want PostgreSQL to execute:

 CREATE TABLE t_person (id serial,                 name text,                 zip_code int4,                 city text,                         PRIMARY KEY (id)); CREATE TABLE t_children (id serial,                 pers_id int4,                 name text,                         PRIMARY KEY(id)); 

This code can easily be executed by sending it to psql using the < symbol in your favorite Unix shell:

 [postgres@athlon postgres]$ psql phpbook < code.sql NOTICE:  CREATE TABLE will create implicit sequence 't_person_id_seq' for SERIAL column 't_person.id' NOTICE:  CREATE TABLE/PRIMARY KEY will create implicit index 't_person_pkey' for table 't_person' CREATE NOTICE:  CREATE TABLE will create implicit sequence 't_children_id_seq' for SERIAL column 't_children.id' NOTICE:  CREATE TABLE/PRIMARY KEY will create implicit index 't_children_pkey' for table 't_children' CREATE 

Two tables have been created. The first columns of the two tables are defined as serial. Serials are a data type for generating a consecutively numbered list. In this case, the first column will contain a unique number that can be used to identify every record in the table. Because the first column of both tables is unique, it can be used as primary key. A column defined as a primary key must always have a unique value duplicated entries are not allowed. As you can see, serial causes PostgreSQL to generate an implicit sequence that makes sure that every value can only occur once.

Documentation is the key to success. When creating more than just one table on the system, it is useful to add comments to the data structure so that other people can easily understand what the various fields are good for. PostgreSQL provides an easy way to add comments to a data structure. In the case of the CREATE TABLE command, comments can easily be added as shown in the next listing:

 CREATE TABLE t_person (id serial,               -- serial number                 name text,                      -- name of the person                 zip_code int4,                  -- postcode                 city text,                      -- name of the city where the                                                 -- person lives                         PRIMARY KEY (id)); CREATE TABLE t_children (id serial,             -- serial number                 pers_id int4,                   -- id of one parent                 name text,                      -- name of the child                         PRIMARY KEY(id)); 

If you want to comment one of your objects after you have created it, PostgreSQL provides a command called COMMENT. COMMENT is a powerful command and can be used to add comments to any object in your database. We recommend adding at least a description of the content of the columns in your tables. This way it is easy for other people to understand what you have done and how your data structure works.

Take a look at the syntax overview of COMMENT:

 phpbook=# \h COMMENT Command:     COMMENT Description: define or change the comment of an object Syntax: COMMENT ON [   [ DATABASE | INDEX | RULE | SEQUENCE | TABLE | TYPE | VIEW ] object_name |   COLUMN table_name.column_name |   AGGREGATE agg_name (agg_type) |   FUNCTION func_name (arg1, arg2, ...) |   OPERATOR op (leftoperand_type rightoperand_type) |   TRIGGER trigger_name ON table_name ] IS 'text' 

As you can see, comments can be defined for all kinds of objects in your database, including tables, triggers, and views. In the next example you will see how a comment can be added to a table:

 phpbook=# COMMENT ON TABLE t_person IS 'this is a comment on the table'; COMMENT 

The only thing you have to tell PostgreSQL is on which table the comment should be added and what string will be used as the comment. After adding the comment, you can retrieve it from the database by using a psql built-in command:

 phpbook=# \dd t_person                 Object descriptions    Name   | Object |          Description ----------+--------+--------------------------------  t_person | table  | this is a comment on the table (1 row) 

As you can see, \dd will cause psql to display the comments defined on the table.

Comments on columns can be added to a table pretty much the same way as comments on tables:

 phpbook=# COMMENT ON COLUMN t_person.zip_code IS 'this is a comment on a column'; COMMENT 

The syntax of the command does not differ.