Previous Topic

Next Topic

Create Statement

You use the create statement with Ingres Star to create new tables and views and add them to your distributed database.

Previous Topic

Next Topic

Create Table Statement

Use the create table statement to create new tables:

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.

Previous Topic

Next Topic

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]]

Previous Topic

Next Topic

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]]

Previous Topic

Next Topic

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.

Previous Topic

Next Topic

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.

Previous Topic

Next Topic

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

Previous Topic

Next Topic

Create View Statement

The create view statement creates a distributed view.

The created view is not a local database view. It is a distributed view known only to the distributed database.

Previous Topic

Next Topic

Create View Syntax

The create view statement has the following format:

create view view_name
       [(col_name {,col_name})] as subselect

For a description of the subselect and other syntax details of the create view statement, see the SQL Reference Guide.


© 2007 Ingres Corporation. All rights reserved.