Cover by Leo Hsu, Regina Obe

Safari, the world’s most comprehensive technology and business learning platform.

Find the exact information you need to solve a problem on the fly, or go deeper to master the technologies and skills you need to succeed

Start Free Trial

No credit card required

O'Reilly logo

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 1, user_name varchar(50) 2
 , description text 3
 , log_ts timestamp with time zone NOT NULL DEFAULT current_timestamp); 4
CREATE INDEX idx_logs_log_ts ON logs USING btree(log_ts);
1

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 ...

Find the exact information you need to solve a problem on the fly, or go deeper to master the technologies and skills you need to succeed

Start Free Trial

No credit card required