The CREATE TABLE statement can also specify a load method. The names in column-name-list and queried columns must be the same in number. If you omit this list, Vertica uses the query output column names. Valid only when creating a table from a query ( AS query), defines column names that map to the query output. If a CREATE TEMPORARY TABLE statement sets k‑num greater than 0, Vertica returns an warning. For general information, see K-Safety in Vertica Concepts.Įon Mode: K‑safety of temporary tables is always set to 0, regardless of system K‑safety. If you omit this option, the projection uses the system K-safety level. Invalid for external tables, specifies K-safety of auto-projections created for this table, where k‑num must be equal to or greater than system K‑safety. If this clause is omitted, Vertica generates auto-projections with default hash segmentation. unsegmented‑clause: Specifies to create an unsegmented projection.For details, see Hash Segmentation Clause. Vertica recommends segmenting large tables. hash‑segmentation‑clause: Specifies to segment data evenly and distribute across cluster nodes.
#Dbvisualizer unique constraint how to
Invalid for external tables, specifies how to distribute data for auto-projections of this table. If you omit the ORDER BY clause, Vertica uses the SELECT list order as the projection sort order. Vertica always stores projection data in ascending sort order. The ORDER BY clause cannot include qualifiers ASC or DESC. Invalid for external tables, specifies columns from the SELECT list on which to sort the superprojection that is automatically created for this table. EXCLUDE PRIVILEGES disables inheritance of privileges from the schema.įor details, see Inherited Privileges in the Administrator's Guide.This is the default behavior if privileges inheritance is enabled for the schema. INCLUDE PRIVILEGES specifies that the table inherits privileges that are set on its schema.Specifies default inheritance of schema privileges for this table: Any segmentation clause ( hash-segmentation-clause or unsegmented‑clause).NO PROJECTION is invalid with the following clauses: A superprojection is created only when data is explicitly loaded into this table. Prevents Vertica from creating auto-projections for this table. TRICKLE: Loads data only into WOS, suitable for frequent incremental loads.įor details, see Choosing a Load Method in the Administrator's Guide.DIRECT: Loads data directly into ROS containers, suitable for large (>100 MB) bulk loads.AUTO (default): Initially loads data into WOS, suitable for smaller bulk loads.Specifies default load behavior for all DML operations on this table, such as INSERT and COPY, one of the following: Vertica removes all table data when the session ends. PRESERVE marks the temporary table for session-scoped data, which is preserved beyond the lifetime of a single transaction.Vertica removes all table data after each commit. DELETE (default) marks the temporary table for transaction-scoped data.If schema is any schema other than public, you must supply the schema name. The IF NOT EXISTS clause is useful for SQL scripts where you want to create an object if it does not already exist, and reuse the existing object if it does.įor related information, see ON_ERROR_STOP. In both cases, the object is not created. If you omit this option and the object exists, Vertica generates a ROLLBACK error message. Specifies to generate an informational message if an object already exists under the specified name. Regardless of this setting, retention of temporary table data is set by the keywords ON COMMIT DELETE and ON COMMIT PRESERVE (see below).įor more information, see Creating Temporary Tables in the Administrator's Guide. LOCAL: the table definition is visible only to the session in which it is created, and is dropped when the session ends.GLOBAL (default): The table definition is visible to all sessions, and persists until you explicitly drop the table.Specifies visibility of the table definition:
0 kommentar(er)
