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

table_constraint

column_constraint

Grammar

create_table ::= CREATE TABLE qualified_name ( table_element [, ...] );

table_element ::= table_column | table_constraints

table_column ::= name column_type [ column_constraint [, ...] | default_value ]

column_constraint ::= PRIMARY KEY | NOT NULL | CHECK ( expression )

default_value ::= DEFAULT expression

table_constraints ::= PRIMARY KEY ( column_list )

column_list ::= name [, ...]

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.

Semantics

• An error is raised if qualified_name already exists in the specified database.

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.

Examples

Example 1

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

Example 2

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

Example 3

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

See Also

DROP TABLE ALTER TABLE INSERT SELECT Other YSQL Statements