Tables, Constraints, and Indexes - PostgreSQL: Up and Running - page 108

Example 6-1. Basic table creation
CREATE TABLE logs ( log_id serial PRIMARY KEY , user_name varchar ( 50 ), descrip
tion 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 is the data type used to represent an incrementing autonumber. Adding
a serial column automatically adds an accompanying sequence object to the
database schema. A serial data type is always an integer with the default value
set to the next value of the sequence object. Each table usually has just one serial
column, which often serves as the primary key.
varchar is shorthand for character varying , a variable-length string similar
to what you will find in other databases. You don't need to specify a maximum
length; if you don't, varchar is almost identical to the text data type.
text is a string of indeterminate length. It's never followed by a length restriction.
timestamp with time zone (shorthand timestamptz ) is a date and time data
type, always stored in UTC. It always displays date and time in the server's own
time zone unless you tell it to otherwise. See “Time Zones: What They Are and
Are Not” on page 86 for a a more thorough discussion.
Inherited Tables
PostgreSQL stands alone as the only database offering inherited tables. When you spec‐
ify that a table (the child table) inherit from another table (the parent table), PostgreSQL
creates the child table with its own columns plus all the columns of the parent table(s).
PostgreSQL will remember this parent-child relationship so that any structural changes
later made to the parent automatically propagate to its children. Parent-child table de‐
sign is perfect for partitioning your data. When you query the parent table, PostgreSQL
automatically includes all rows in the child tables. Not every trait of the parent passes
down to the child. Notably, primary key constraints, uniqueness constraints, and in‐
dexes are never inherited. Check constraints are inherited, but children can have their
own check constraints in addition to the ones they inherit from their parents (see
Example 6-2 ).
Example 6-2. Inherited table creation
CREATE TABLE logs_2011 ( PRIMARY KEY ( log_id )) INHERITS ( logs );
CREATE INDEX idx_logs_2011_log_ts ON logs USING btree ( log_ts );
ALTER TABLE logs_2011 ADD CONSTRAINT chk_y2011
CHECK ( log_ts >= '2011-1-1' :: timestamptz
AND log_ts < '2012-1-1' :: timestamptz );