CREATE TABLE v14

Name

CREATE TABLE — Define a new table.

Synopsis

CREATE [ GLOBAL TEMPORARY | UNLOGGED ] TABLE <table_name> (
  { <column_name> <data_type> [ DEFAULT <default_expr> ]
  [ <column_constraint> [ ... ] ] | <table_constraint> } [, ...]
  )
  [ WITH ( ROWIDS [= <value> ] ) ]
  [ ON COMMIT { PRESERVE ROWS | DELETE ROWS } ]
  [ TABLESPACE <tablespace> ]
  { NOPARALLEL | PARALLEL [ <integer> ] }

Where column_constraint is:

[ CONSTRAINT <constraint_name> ]
{ NOT NULL |
  NULL |
  UNIQUE [ USING INDEX TABLESPACE <tablespace> ] |
  PRIMARY KEY [ USING INDEX TABLESPACE <tablespace> ] |
  CHECK (<expression>) |
  REFERENCES <reftable> [ ( <refcolumn> ) ]
    [ ON DELETE <action> ] }
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED |
  INITIALLY IMMEDIATE ]

Where table_constraint is:

[ CONSTRAINT <constraint_name> ]
{ UNIQUE ( <column_name> [, ...] )
    [ USING INDEX [ <create_index_statement> ] TABLESPACE <tablespace> ] |
  PRIMARY KEY ( <column_name> [, ...] )
    [ USING INDEX [ <create_index_statement> ] TABLESPACE <tablespace> ] |
  CHECK ( <expression> ) |
  FOREIGN KEY ( <column_name> [, ...] )
      REFERENCES <reftable> [ ( <refcolumn> [, ...] ) ]
    [ ON DELETE <action> ] }
[ DEFERRABLE | NOT DEFERRABLE ]
[ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

Description

CREATE TABLE creates an empty table in the current database. The user who creates the table owns the table.

If you provide a schema name, then the table is created in the specified schema. Otherwise it's created in the current schema. Temporary tables exist in a special schema, so you can't provide a schema name when creating a temporary table. The table name must differ from the name of any table, sequence, index, or view in the same schema.

CREATE TABLE also creates a data type that represents the composite type corresponding to one row of the table. Therefore, tables can't have the same name as any existing data type in the same schema.

The PARALLEL clause sets the degree of parallelism for a table. If you don't specify the PARALLEL clause, the server determines a value based on the relation size.

The NOPARALLEL clause resets the parallelism for default execution, and reloptions shows the parallel_workers parameter as 0.

A table can't have more than 1600 columns. In practice, the effective limit is lower because of tuple-length constraints.

The optional constraint clauses specify constraints or tests that new or updated rows must satisfy for an insert or update operation to succeed. A constraint is a SQL object that helps define the set of valid values in the table in various ways.

You can define table constraints and column constraints. A column constraint is part of a column definition. A table constraint definition isn't tied to a particular column, and it can encompass more than one column. You can also write every column constraint as a table constraint. A column constraint is a notational convenience only if the constraint affects only one column.

Parameters

GLOBAL TEMPORARY

Creates the table as a temporary table. Temporary tables are dropped at the end of a session or, optionally, at the end of the current transaction. (See the ON COMMIT parameter.) Existing permanent tables with the same name aren't visible to the current session while the temporary table exists unless you reference them with schema-qualified names. In addition, temporary tables aren't visible outside the session in which you created them. This aspect of global temporary tables isn't compatible with Oracle databases. Any indexes created on a temporary table are also temporary.

UNLOGGED

Creates the table as an unlogged table. The data written to unlogged tables isn't written to the write-ahead log (WAL), making them faster than an ordinary table. Indexes created on an unlogged table are unlogged. The contents of an unlogged table aren't replicated to a standby server. The unlogged table is not crash-safe. It's truncated after a crash or shutdown.

table_name

The name (optionally schema-qualified) of the table to create.

column_name

The name of a column to create in the new table.

data_type

The data type of the column. This can include array specifiers. For more information on the data types included with EDB Postgres Advanced Server, see SQL reference.

DEFAULT default_expr

The DEFAULT clause assigns a default data value for the column whose column definition it appears in. The value is any variable-free expression. Subqueries and cross references to other columns in the current table aren't allowed. The data type of the default expression must match the data type of the column.

The default expression is used in any insert operation that doesn't specify a value for the column. If you don't specify a default for a column, then the default is null.

CONSTRAINT constraint_name

An optional name for a column or table constraint. If you don't specify one, the system generates a name.

NOT NULL

The column can't contain null values.

NULL

The column can contain null values. This is the default.

This clause is available only for compatibility with nonstandard SQL databases. We don't recommend using it in new applications.

UNIQUE — Column constraint.

UNIQUE (column_name [, ...] ) — Table constraint.

The UNIQUE constraint specifies that a group of one or more distinct columns of a table can contain only unique values. The behavior of the unique table constraint is the same as that for column constraints. However, the unique table constraint can span multiple columns.

For the purpose of a unique constraint, null values aren't considered equal.

Each unique table constraint must name a set of columns that's different from the set of columns named by any other unique or primary key constraint defined for the table. Otherwise it's the same constraint listed twice.

PRIMARY KEY — Column constraint.

PRIMARY KEY ( column_name [, ...] ) — Table constraint.

The primary key constraint specifies that any columns of a table can contain only unique, non-duplicated, non-null values. Technically, PRIMARY KEY is a combination of UNIQUE and NOT NULL. However, identifying a set of columns as primary key also provides metadata about the design of the schema. A primary key implies that other tables might rely on this set of columns as a unique identifier for rows.

You can specify only one primary key for a table, whether as a column constraint or a table constraint.

Use the primary key constraint to name a set of columns that's different from other sets of columns named by any unique constraint defined for the same table.

CHECK (expression)

The CHECK clause specifies an expression producing a Boolean result that new or updated rows must satisfy for an insert or update operation to succeed. Expressions evaluating to TRUE or “unknown” succeed. If any row of an insert or update operation produces a FALSE result, an error exception is raised and the insert or update doesn't alter the database. A check constraint specified as a column constraint must reference only that column’s value. An expression appearing in a table constraint can reference multiple columns.

Currently, CHECK expressions can't contain subqueries or refer to variables other than columns of the current row.

REFERENCES reftable [ ( refcolumn) ] [ ON DELETE action ] — Column constraint.

FOREIGN KEY ( column [, ...] ) REFERENCES reftable [ ( refcolumn [, ...] ) ] [ ON DELETE action ] — Table constraint.

These clauses specify a foreign-key constraint, which requires that a group of columns of the new table must contain only values that match values in the referenced columns of some row of the referenced table. If you omit refcolumn, the primary key of the reftable is used. The referenced columns must be the columns of a unique or primary key constraint in the referenced table.

In addition, when the data in the referenced columns changes, certain actions are performed on the data in this table’s columns. The ON DELETE clause specifies the action to perform when a referenced row in the referenced table is being deleted. You can't defer referential actions even if the constraint is deferrable. Here are the following possible actions for each clause:

• CASCADE

  Delete any rows referencing the deleted row, or update the value of the referencing column to the new value of the referenced column.

• SET NULL

  Set the referencing columns to NULL.

  If the referenced columns change frequently, we recommend adding an index to the foreign key column so that referential actions associated with the foreign key column are performed more efficiently.

DEFERRABLE

NOT DEFERRABLE

This parameter controls whether you can defer the constraint. A constraint that isn't deferrable is checked immediately after every command. You can postpone checking constraints that are deferrable until the end of the transaction using the SET CONSTRAINTS command. NOT DEFERRABLE is the default. Only foreign key constraints currently accept this clause. All other constraint types aren't deferrable.

INITIALLY IMMEDIATE

INITIALLY DEFERRED

If a constraint is deferrable, this clause specifies the default time to check the constraint. If the constraint is INITIALLY IMMEDIATE, it's checked after each statement. This is the default. If the constraint is INITIALLY DEFERRED, it's checked only at the end of the transaction. You can alter the constraint check time with the SET CONSTRAINTS command.

WITH ( ROWIDS [= value ] )

The ROWIDS option for a table includes value equals to TRUE/ON/1 or FALSE/OFF/0. When set to TRUE/ON/1, a ROWID column is created in the new table. ROWID is an auto-incrementing value based on a sequence that starts with 1 and assigned to each row of a table. The default value is always TRUE.

By default, a unique index is created on a ROWID column. The ALTER and DROP operations are restricted on a ROWID column.

ON COMMIT

You can control the behavior of temporary tables at the end of a transaction block using ON COMMIT. The options are:

• PRESERVE ROWS

  No special action is taken at the ends of transactions. This is the default behavior. This aspect isn't compatible with Oracle databases. The Oracle default is DELETE ROWS.

• DELETE ROWS

  All rows in the temporary table are deleted at the end of each transaction block. Essentially, an automatic TRUNCATE is done at each commit.

TABLESPACE tablespace

The tablespace is the name of the tablespace where the new table is created. If not specified, default tablespace is used or the database’s default tablespace if default_tablespace is an empty string.

USING INDEX [ create_index_statement ] TABLESPACE tablespace

This clause allows selection of the tablespace in which the index associated with a UNIQUE or PRIMARY KEY constraint is created. If not specified, default tablespace is used, or the database’s default tablespace if default_tablespace is an empty string.

If you specify the create_index_statement option, the database server creates an index enabling unique or primary key constraints. The columns specified in the constraint and the columns of an index must be the same, but their order of appearance might differ.

PARALLEL

Include the PARALLEL clause to specify the degree of parallelism for the table. Set the parallel_workers parameter equal to the degree of parallelism to perform a parallel scan of a table. Alternatively, if you specify PARALLEL but don't include a degree of parallelism, an index uses default parallelism.

NOPARALLEL

Specify NOPARALLEL for default execution.

integer

The integer indicates the degree of parallelism, which is a number of parallel_workers used in the parallel operation to perform a parallel scan on a table.

Notes

EDB Postgres Advanced Server creates an index for each unique constraint and primary key constraint to enforce the uniqueness. Thus, you don't need to create an explicit index for primary key columns. For more information, see CREATE INDEX.

Examples

Create table dept and table emp:

CREATE TABLE dept (
    deptno          NUMBER(2) NOT NULL CONSTRAINT dept_pk PRIMARY KEY,
    dname           VARCHAR2(14),
    loc             VARCHAR2(13)
);
CREATE TABLE emp (
    empno           NUMBER(4) NOT NULL CONSTRAINT emp_pk PRIMARY KEY,
    ename           VARCHAR2(10),
    job             VARCHAR2(9),
    mgr             NUMBER(4),
    hiredate        DATE,
    sal             NUMBER(7,2),
    comm            NUMBER(7,2),
    deptno          NUMBER(2) CONSTRAINT emp_ref_dept_fk
                        REFERENCES dept(deptno)
);

Define a unique table constraint for the table dept. You can define unique table constraints on one or more columns of the table.

CREATE TABLE dept (
    deptno          NUMBER(2) NOT NULL CONSTRAINT dept_pk PRIMARY KEY,
    dname           VARCHAR2(14) CONSTRAINT dept_dname_uq UNIQUE,
    loc             VARCHAR2(13)
);

Define a check column constraint:

CREATE TABLE emp (
    empno           NUMBER(4) NOT NULL CONSTRAINT emp_pk PRIMARY KEY,
    ename           VARCHAR2(10),
    job             VARCHAR2(9),
    mgr             NUMBER(4),
    hiredate        DATE,
    sal             NUMBER(7,2) CONSTRAINT emp_sal_ck CHECK (sal > 0),
    comm            NUMBER(7,2),
    deptno          NUMBER(2) CONSTRAINT emp_ref_dept_fk
                        REFERENCES dept(deptno)
);

Define a check table constraint:

CREATE TABLE emp (
    empno           NUMBER(4) NOT NULL CONSTRAINT emp_pk PRIMARY KEY,
    ename           VARCHAR2(10),
    job             VARCHAR2(9),
    mgr             NUMBER(4),
    hiredate        DATE,
    sal             NUMBER(7,2),
    comm            NUMBER(7,2),
    deptno          NUMBER(2) CONSTRAINT emp_ref_dept_fk
                        REFERENCES dept(deptno),
    CONSTRAINT new_emp_ck CHECK (ename IS NOT NULL AND empno > 7000)
);

Define a primary key table constraint for the table jobhist. You can define primary key table constraints on one or more columns of the table.

CREATE TABLE jobhist (
    empno           NUMBER(4) NOT NULL,
    startdate       DATE NOT NULL,
    enddate         DATE,
    job             VARCHAR2(9),
    sal             NUMBER(7,2),
    comm            NUMBER(7,2),
    deptno          NUMBER(2),
    chgdesc         VARCHAR2(80),
    CONSTRAINT jobhist_pk PRIMARY KEY (empno, startdate)
);

This example assigns a literal constant default value for the column job and makes the default value of hiredate the date when the row is inserted.

CREATE TABLE emp (
    empno           NUMBER(4) NOT NULL CONSTRAINT emp_pk PRIMARY KEY,
    ename           VARCHAR2(10),
    job             VARCHAR2(9) DEFAULT 'SALESMAN',
    mgr             NUMBER(4),
    hiredate        DATE DEFAULT SYSDATE,
    sal             NUMBER(7,2),
    comm            NUMBER(7,2),
    deptno          NUMBER(2) CONSTRAINT emp_ref_dept_fk
                        REFERENCES dept(deptno)
);

Create table dept in tablespace diskvol1:

CREATE TABLE dept (
    deptno          NUMBER(2) NOT NULL CONSTRAINT dept_pk PRIMARY KEY,
    dname           VARCHAR2(14),
    loc             VARCHAR2(13)
) TABLESPACE diskvol1;

This PARALLEL example creates a table sales and sets a degree of parallelism to 6:

CREATE TABLE sales (deptno number) PARALLEL 6 WITH (FILLFACTOR=66);

This NOPARALLEL example creates a table sales_order and sets a degree of parallelism to 0:

CREATE TABLE sales_order (deptno number) NOPARALLEL WITH (FILLFACTOR=66);

This example creates a table named dept. The definition creates a unique key on the dname column. The constraint dept_dname_uq identifies the dname column as a unique key. The preceding statement includes the USING_INDEX clause, which explicitly creates an index on a table dept with the index statement enabling the unique constraint.

CREATE TABLE dept (
    deptno          NUMBER(2) NOT NULL,
    dname           VARCHAR2(14),
    loc             VARCHAR2(13),
    CONSTRAINT dept_dname_uq UNIQUE(dname)
       USING INDEX (CREATE UNIQUE INDEX idx_dept_dname_uq ON dept(dname))
);

This example creates a table named emp. The definition creates a primary key on the ename column. The emp_ename_pk constraint identifies the ename column as a primary key of the emp table. The preceding statement includes the USING_INDEX clause, which explicitly creates an index on a table emp with the index statement enabling the primary constraint.

CREATE TABLE emp (
    empno           NUMBER(4) NOT NULL,
    ename           VARCHAR2(10),
    job             VARCHAR2(9),
    sal             NUMBER(7,2),
    deptno          NUMBER(2),
    CONSTRAINT emp_ename_pk PRIMARY KEY (ename)
        USING INDEX (CREATE INDEX idx_emp_ename_pk ON emp (ename))
);

See also

ALTER TABLE, DROP TABLE