CREATE TABLE
Attention
This page documents an earlier version. Go to the latest (v2.3) version.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
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 ) ]
create_table
table_elem
column_constraint
table_constraint
storage_parameters
storage_parameter
index_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
ortable_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);