Was this helpful?
CREATE TABLE
Valid in: SQL, ESQL, OpenAPI, ODBC, JDBC, .NET
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.
The CREATE TABLE statement has the following format:
[EXEC SQL] CREATE TABLE [IF NOT EXISTS] [schema.] tablename
              (column_specification {, column_specification }
              [, [CONSTRAINT constraint_name] table_constraint
              {, [CONSTRAINT constraint_name] table_constraint}])
              [with_clause]
The CREATE TABLE...AS SELECT statement (which creates a table and loads rows from another table) has the following format:
[EXEC SQL] CREATE TABLE tablename
              (column_name {, column_name}) AS
                            [WITH common_table_expression] subselect
                            {UNION [ALL]
                            subselect}
            [with_clause]
IF NOT EXISTS
Creates the table if it does not exist and returns without error if the table already exists.
Note:  Use this option with caution. The table definition of a preexisting table may differ from that of the CREATE TABLE IF NOT EXISTS statement.
tablename
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 Specification--Define Column Characteristics.
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
SECURITY_AUDIT = (audit_opt {, audit_opt})
SECURITY_AUDIT_KEY = (column)
NOPARTITION | PARTITION = ( partitioning-scheme )
[NO]AUTOSTRUCT
COMPRESSION[= ([[NO]KEY] [,[NO]DATA])] | NOCOMPRESSION*
ALLOCATION = n
EXTEND = n
PRIORITY = n
ENCRYPTION=AES128|AES192|AES256, [AESKEY=hex-aes-key,] PASSPHRASE='encryption-passphrase'
STRUCTURE = structure
Additional options on the WITH clause for CREATE TABLE...AS SELECT (see 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
AS [WITH common_table_expression] subselect
(Valid in CREATE TABLE AS SELECT only.)
Specifies a SELECT clause, as described 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).
The subselect can be preceded by a common table expression (see WITH (common_table_expression)).
Last modified date: 08/29/2024