CREATE TABLE

Synopsis

Use the CREATE TABLE statement to create a new table in a database. It defines the table name, column names and types, primary key, and table properties.

Syntax

• Grammar
• Diagram

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

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

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

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

storage_parameters ::= storage_parameter [ , ... ]

storage_parameter ::= param_name [ = param_value ]

index_parameters ::= [ INCLUDE ( column_names ) ] 
                     [ WITH ( storage_parameters ) ]

Semantics

create_table

CREATE TABLE [ IF NOT EXISTS ] table_name

Create a table with table_name. An error is raised if qualified_name already exists in the specified database.

table_elem

column_constraint

CONSTRAINT constraint_name

Specify the name of the constraint.

table_constraint

CONSTRAINT constraint_name

NOT NULL | NULL | CHECK ( expression ) | DEFAULT expression | UNIQUE index_parameters | PRIMARY KEY | references_clause

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.
• Each row in a table is uniquely identified by its primary key.

FOREIGN KEY

Foreign keys are supported starting v1.2.10.

storage_parameter

Represent storage parameters as defined by PostgreSQL.

name | name = value

For DEFAULT keyword must be of the same type as the column it modifies. It must be of type boolean for CHECK constraints.

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);

Table with unique constraint

postgres=# CREATE TABLE translations(message_id int UNIQUE,
                                     message_txt text);

See also

• ALTER TABLE
• CREATE TABLE AS
• CREATE TABLESPACE
• DROP TABLE