Create Table Statement
Use the create table statement to create new tables:
• create table with with_clause
• create table as subselect with with_clause
The create table statement creates an object of type table. The table is stored in a local database or the coordinator database, depending on the node_name specified in the with clause. By default, the table is stored in the coordinator database. The table is automatically registered in the Ingres Star catalogs as a native object. Tables registered as native are distinguished from tables registered as links by the value in the table_subtype column in the iitables catalog.
Create Table With Syntax
The create table with statement has the following format:
create table table_name
(col_name format {, col_name format})
[with
[node =node_name,
database = database_name]
[, dbms = server_class]
[, table =local_table_name]
[, LDB with clauses]]
Create Table as Subselect With Syntax
The syntax for the create table as subselect with statement is:
create table table_name
[(col_name {, col_name})]
as subselect
[with
[node =node_name,
database =database_name]
[, dbms = server_class]
[, table = local_table_name]
[, LDB with clauses]]
Create Table Syntax Elements
The col_name, node_name, database_name, server_class, and local_table_name syntax elements are as described in previous statements. The remaining syntax elements are described in the table below and in the following section, LDB With Clauses.
table
The object identifier. This must be specified.
table_name
The name in the distributed database. It can be delimited with double quotes.
If the local_table_name is not specified the registered table_name is used. The table names referenced in the subselect clause are Ingres Star-level objects. They must be registered or created first through Ingres Star before being called by subselect
format
Formats refer to the data type of the column as well as how unspecified values (blanks and nulls) should be handled. For a full description of data formats, see the SQL Reference Guide.
Note: Creating a table at the Star level that contains Unicode (nchar and nvarchar) data types is not supported. You can, however, REGISTER...AS LINK a table that contains Unicode columns from a source database.
LDB With Clauses
Ingres Star recognizes and processes with clauses for defining node_name, database_name, server_class, and local_table_name as shown in the syntax descriptions above.
Other with clauses (LDB with clauses) in the syntax descriptions above) are not supported or recognized by Ingres Star. For example, Ingres Star is not responsible for handling location, journaling and duplicates in the with clause. However, it is Ingres Star’s responsibility to guarantee that these clauses are properly transmitted to the local DBMS for processing. These options are received and managed by each local DBMS.
For a complete list of the LDB with clauses, see the with clauses of the create table description in your query language reference guide.
Examples: Create Table
The following statement creates the table corp_dept in the coordinator database and registers it in the distributed database:
create table corp_dept
(dno char(8),
name char(10),
budget integer);
Create the table department in the database west_usa on the node reno and register it in the distributed database corporateddb under the name corp_dept:
create table corp_dept
(dno char(8),
name char(10),
budget integer)
with node = reno,
database = west_usa,
table = department;
A table low_budgets could then be created in the coordinator database and registered in the distributed database by using a subselect from the table corp_dept:
create table low_budgets
as select * from corp_dept
where budget < 10000;
The following example shows a create table statement with an LDB with clause, journaling:
create table corpemployee
(name char(20),
sal money)
with journaling;
Journaling is set at the local DBMS. Ingres Star does not register this attribute in the Ingres Star catalogs but passes it to the local DBMS to be processed.
If your Star database allows mixed-case delimited identifiers or if you wish your table or column names to include special characters such as spaces, use a delimited identifier:
create table "World Wide Sales" ("Region" char(20),
"Gross Sales in Millions" decimal(16,2));