Create Table

Valid in: SQL, ESQL

The CREATE TABLE statement creates a base table.

Note: This statement has additional considerations when used in a distributed environment. For more information, see the Ingres Star User Guide.

Syntax

The CREATE TABLE statement has the following format:

[EXEC SQL] CREATE TABLE [schema.] table_name

              (column_specification {, column_specification }

              [, [CONSTRAINT constraint_name] table_constraint

              {, [CONSTRAINT constraint_name] table_constraint}])

              [WITH with_clause]

The CREATE TABLE...AS SELECT statement (which creates a table and load rows from another table) has the following format:

[EXEC SQL] CREATE TABLE table_name
              (column_name {, column_name}) AS
                            subselect
                            {UNION [ALL]
                            subselect}
              [WITH with_clause]

• table_name

  Defines the name of the new table, which must be a valid object name.

• column_specification

  Defines the characteristics of the column, as described in Column Specifications.

• table_constraint

  Specifies the table-level constraint as described in Column-Level Constraints and Table-Level Constraints.

• with_clause

  Consists of a comma-separated list of one or more of the following options, described in detail in With_Clause for Create Table:

  • LOCATION = (location_name {, location_name})
  • [NO]JOURNALING
  • [NO]DUPLICATES
  • PAGE_SIZE = n
  • LABEL_GRANULARITY = TABLE | ROW | SYSTEM_DEFAULT
  • SECURITY_AUDIT = (audit_opt {, audit_opt})
  • SECURITY_AUDIT_KEY = (column)
  • NOPARTITION | PARTITION = ( partitioning-scheme )

    Additional options on the With_Clause for Create Table...as Select are as follows:

  • STRUCTURE = HASH | HEAP | ISAM | BTREE
  • KEY = (column_name {, column_name})
  • FILLFACTOR = n
  • MINPAGES = n
  • MAXPAGES = n
  • LEAFFILL = n
  • NONLEAFFILL = n
  • COMPRESSION[= ([[NO]KEY] [,[NO]DATA])] | NOCOMPRESSION
  • ALLOCATION = n
  • EXTEND = n
  • PRIORITY = n
• subselect

  Specifies a SELECT clause, described in detail in Select (interactive). Also see Using Create Table...As Select.

  Note: Subselect cannot be used when creating a table in one or more raw locations—that is, CREATE TABLE raw_table AS SELECT... WITH LOCATION = (raw_loc).

Description

The CREATE TABLE statement creates a base table. A base table contains rows independently of other tables (unlike a view, which has no independent existence of its own). Rows in a base table are added, changed, and deleted by the user (unlike an index, which is automatically maintained by the DBMS Server).

The default page size is the smaller of either 2048 (2 KB)—unless changed by the system administrator—or the smallest page size configured in the system that holds the record. For example, assume that an installation is configured to allocate buffer pools for 2048 (2 KB), 8192 (8 KB), and 65536 (64 KB). A table is created with a row size of 2500 bytes, and if a specific page size is not requested, the table is created with an 8 KB page size. Similarly, if 4096 byte (4 KB) pages are also configured, the table is created with 4 KB pages instead of 8 KB pages because 4 KB is the smallest configured page size capable of containing the row. If the row is larger than any page size configured, or if a page size too small is specified with the page_size clause, the table create fails.

The default storage structure for tables is HEAP. To create a table that has a different storage structure, specify the STRUCTURE option in the WITH clause.

To create a table that is populated with data from another table, specify CREATE TABLE...AS SELECT. The resulting table contains the results of the select statement.

By default, tables are created without an expiration date. To specify an expiration date for a table, use the SAVE statement. To delete expired tables, use the verifydb utility. For details, see the System Administrator Guide.

A maximum of 1024 columns can be specified for a base table.

The following table shows the maximum row length when rows do not span pages.

You can create a table with row size greater than the maximum documented above, up to 256 KB. If the WITH PAGE_SIZE clause is not specified, the table is created with the default page size.

Note: Ingres is more efficient when row size is less than or equal to the maximum row size for the corresponding page size.

Long varchar and long byte columns can contain a maximum of 2 GB characters and bytes, respectively. The length of long varchar or long byte columns cannot be specified.

The following data types require space in addition to their declared size:

• A varchar or text column consumes two bytes (in addition to its declared length) to store the length of the string.
• Nullable columns require one additional byte to store the null indicator.
• In tables created with compression, c columns require one byte in addition to the declared length, and char columns require two additional bytes.

Note: If II_DECIMAL is set to comma, be sure that when SQL syntax requires a comma (such as a list of table columns or SQL functions with several parameters), that the comma is followed by a space. For example:

select col1, ifnull(col2, 0), left(col4, 22) from t1: