Syntax: 

CREATE | REPLACE [ COMPRESSED ] TABLE [ IF NOT EXISTS ] [catalog-name.]table-name (column-def[, column-def]*[, constraint]*)

CREATE | REPLACE [ COMPRESSED ] TABLE table-name AS select-statement

column-def:
name[type][[CONSTRAINT name] column-constraint]*

type:
typename | typename(number) | typename(number, number)

column-constraint:
NOT NULL [conflict-clause] |
PRIMARY KEY [sort-order] [conflict-clause] [AUTOINCREMENT] |
UNIQUE [conflict-clause] |
CHECK (expr) |
DEFAULT value |
COLLATE collation-name

constraint:
PRIMARY KEY (column-list) [conflict-cvlause] |
UNIQUE [conflict-clause] |
CHECK(expr)

conflict-clause:
ON CONFLICT conflict-algorithm
	

The CREATE TABLE statement is used to create new tables in a GDBase database. It is followed by the name of a new table and a parenthesized list of column definitions and constraints. The table name can be either an identifier or a string. Tables names that begin with sqlite_ are not allowed.

The REPLACE TABLE command can be used to replace an existing table or create a new one, if a table with thte spacified name does not exist in the database. Apart from this, the REPLACE TABLE command behaves in the same way as CREATE TABLE.

The optional COMPRESSED causes the created table to be compressed, with each row being compressed separately. In the case of tables with a lot of columns this may lead to up to 2.5 times decrease in the size of the table, while for tables with few columns the gain in space occupied on disk may be much less significant. The compression algorithm always checks whether compressing a row would lead to smaller disk space usage and if not, the row is not compressed.

Each column definition is the name of the column followed by the data type for that column, then one or more optional column constraints. The data type can be omited. In this case the database will conjecture the type form the inserted data. See the Data types section for detailed information about data types supported by GDBase.

The UNIQUE constraint causes an index to be created on the specified columns. This index must contain unique keys. The COLLATE clause specifies what text collating function to use when comparing text entries for the column. The built-in BINARY collating function is used by default.

The DEFAULT constraint specifies the default value to use when doing an INSERT.. The value may be null, a string constant or a number. The default value may also be one of the special case-independant keywords CURRENT_TIME, CURRENT_DATE or CURRENT_TIMESTAMP. If the value is null, a string constant, or number, it is literally inserted into the column whenever an INSERT statement that does not specify a value for the column is executed. If the value is CURRENT_TIME, CURRENT_DATE or CURRENT_TIMESTAMP, then the current UTC date and/or time is inserted into the columns. For CURRENT_TIME, the format is HH:MM:SS. For CURRENT_DATE, YYYY-MM-DD. The format for CURRENT_TIMESTAMP is "YYYY-MM-DD HH:MM:SS".

Specifying a PRIMARY KEY normally just creates a UNIQUE index on the corresponding columns. However, if the primary key is on a single column that has datatype INTEGER, then that column is used internally as the actual key of the B-Tree for the table. This means that the column may only hold unique integer values. If a table does not have an INTEGER PRIMARY KEY column, then the B-Tree key will be an automatically generated integer. The B-Tree key for a row can always be accessed using one of the special names ROWID", OID, or _ROWID_. This is true regardless of whether or not there is an INTEGER PRIMARY KEY. An INTEGER PRIMARY KEY column can also include the AUTOINCREMENT keyword, which modifies the way in which B-Tree keys are automatically generated.

According to the SQL standard, PRIMARY KEY should imply not Null. However GDBase allows Null values in the PRIMARY KEY column.

The optional conflict-clause following each constraint allows the specification of an alternative default constraint conflict resolution algorithm for that constraint. The default is ABORT. Different constraints within the same table may have different default conflict resolution algorithms. If a COPY, INSERT, or UPDATE command specifies a different conflict resolution algorithm, then that algorithm is used in place of the default algorithm specified in the CREATE TABLE statement. See the description of ON CONFLICT clauses for additional information.

There are no arbitrary limits on the number of columns or on the number of constraints in a table. Also there is no arbitrary limit on the amount of data in a row.

The CREATE TABLE AS is used to create the table based on the result of a query. The names of the table columns are the names of the columns in the result.

If the optional IF NOT EXISTS clause is present and another table with the same name aleady exists, then this command becomes a no-op.

Tables are removed using the DROP TABLE command.