PostgreSQL: Up and Running

Chapter 6. Of Tables, Constraints, and Indexes

Tables

In addition to the run-of-the-mill data table, PostgreSQL offers several kinds of tables that are rather unique: temporary, unlogged (demonstrated in Example 6-3), inherited (demonstrated in Example 6-2), and typed tables (demonstrated in Example 6-4).

Table Creation

In this section, we’ll demonstrate some common table creation examples. Most are similar to or exactly what you’ll find in other databases.

Example 6-1. Basic table creation

CREATE TABLE logs(
 log_id serial PRIMARY KEY , user_name varchar(50) 
 , description text 
 , log_ts timestamp with time zone NOT NULL DEFAULT current_timestamp); 
CREATE INDEX idx_logs_log_ts ON logs USING btree(log_ts);

serial type is the data type you use when you want an incrementing auto number. It creates a companion sequence object and defines the new column as an integer with the default value set to the next value of the sequence object. It is often as a primary key.

varchar is a variable length string ...