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 smallest of either 8K (8192 bytes)—unless changed by the system administrator—or the page size configured in the installation that holds the record. For example, if 2K (2048 bytes), 4K (4096 bytes), and 8K (8192 bytes) page sizes are configured and the row size for a table to be created is 2500 bytes, the table is created with an 8K page size. Or, if 2K, 8K and 16K bytes page sizes are configured and the row size for the table to be created is 12,000 bytes, the table is created with a 16K page size.
Note: If the row is larger than any page size configured, or if a page size too small is specified with the page_size clause, Ingres creates the table, but the larger rows will span multiple pages.
The default storage structure for tables is either B-tree or heap, depending on the setting of the table_auto_structure configuration parameter or WITH [NO]AUTOSTRUCT in combination with the presence of constraint definitions in the CREATE TABLE statement.
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, you must follow any comma required in SQL syntax (such as a list of table columns or SQL functions with several parameters) by a space. For example:
SELECT col1, IFNULL(col2, 0), LEFT(col4, 22) FROM version;
Last modified date: 11/28/2023