CREATE TABLE

Synopsis

The CREATE TABLE command creates a new table in a database. It defines the table name, column names and types, primary key, and table properties.

Syntax

Diagrams

create_table

table_elem

column_constraint

table_constraint

storage_parameter

Grammar

create_table ::= CREATE TABLE [ IF NOT EXISTS ] table_name ( [ table_elem [, ...] ] ) [ WITH ( storage_parameter [, ... ] ) | WITHOUT OIDS ]

table_elem ::= column_name data_type [ column_constraint ...] | table_constraint

column_constraint ::= [ CONSTRAINT constraint_name ]
                      { NOT NULL |
                        NULL |
                        CHECK ( expression ) |
                        DEFAULT expression |
                        PRIMARY KEY | 
                        references_clause } ;

table_constraint ::= [ CONSTRAINT constraint_name ] 
                     { CHECK ( expression ) |
                       PRIMARY KEY ( column_names ) |
                       FOREIGN KEY ( column_names ) references_clause }

storage_param ::= param_name [ = param_value ]

Where

qualified_name and name are identifiers (qualified_name can be a qualified name).
expression for DEFAULT keyword must be of the same type as the column it modifies. It must be of type boolean for CHECK constraints.
param_name and param_value represent storage parameters as defined by PostgreSQL

Semantics

An error is raised if qualified_name already exists in the specified database.
Storage parameters through the WITH clause are accepted for PostgreSQL compatibility, but they will be ignored. The exceptions are oids=true and user_catalog_table=true, which are explicitly disallowed and will result in an error being thrown.

PRIMARY KEY

Currently defining a primary key is required.
Primary key can be defined in either column_constraint or table_constraint but not in both of them.
Each row in a table is uniquely identified by its primary key.

FOREIGN KEY

Foreign keys are supported starting v1.2.10.

Examples

Table with primary key

postgres=# CREATE TABLE sample(k1 int, 
                               k2 int, 
                               v1 int, 
                               v2 text, 
                               PRIMARY KEY (k1, k2));

Table with check constraint

postgres=# CREATE TABLE student_grade (student_id int, 
                                       class_id int, 
                                       term_id int, 
                                       grade int CHECK (grade >= 0 AND grade <= 10), 
                                       PRIMARY KEY (student_id, class_id, term_id));

Table with default value

postgres=# CREATE TABLE cars (id int PRIMARY KEY, 
                              brand text CHECK (brand in ('X', 'Y', 'Z')), 
                              model text NOT NULL, 
                              color text NOT NULL DEFAULT 'WHITE' CHECK (color in ('RED', 'WHITE', 'BLUE')));

Table with foreign key constraint

postgres=# create table products(id int primary key, 
                                 descr text);
postgres=# create table orders(id int primary key, 
                               pid int references products(id) ON DELETE CASCADE, 
                               amount int);