Creating a Table from a Query

CREATE TABLE can specify an AS clause, to create a table from a query, as follows:

CREATE [TEMPORARY] TABLE [schema.]table-name 
... [ ( column-name-list ) ]
... [ load-method ]
... [ {INCLUDE | EXCLUDE} [SCHEMA] PRIVILEGES ]  
AS  [  /*+ hint[, hint] */ ] [ AT epoch ] query [ ENCODED BY column‑ref‑list ]

Vertica creates a table from the query results and loads the result set into it. For example:

=> CREATE TABLE cust_basic_profile AS SELECT 
     customer_key, customer_gender, customer_age, marital_status, annual_income, occupation 
     FROM customer_dimension WHERE customer_age>18 AND customer_gender !='';
CREATE TABLE
=> SELECT customer_age, annual_income, occupation FROM cust_basic_profile 
     WHERE customer_age > 23 ORDER BY customer_age;
 customer_age | annual_income |     occupation
--------------+---------------+--------------------
           24 |        469210 | Hairdresser
           24 |        140833 | Butler
           24 |        558867 | Lumberjack
           24 |        529117 | Mechanic
           24 |        322062 | Acrobat
           24 |        213734 | Writer 
           ...

AS Clause Options

You can qualify the AS clause with one or both of the following hints:

• A load method hint: AUTO, DIRECT, or TRICKLE

  Note: The CREATE TABLE statement can also specify a load method. However, this load method applies to load operations only after the table is created.

• LABEL: Assigns a label to a statement to identify it for profiling and debugging. See Labeling the AS Clause below.

You can also specify that the query return historical data with AT epoch. See Loading Historical Data below.

Labeling the AS Clause

You can embed a LABEL hint in an AS clause in two places:

• Immediately after the keyword AS:

  CREATE TABLE myTable AS /*+LABEL myLabel*/...

• In the SELECT statement:

  CREATE TABLE myTable AS SELECT /*+LABEL myLabel*/

If the AS clause contains a LABEL hint in both places, the first label has precedence.

Specifying a Load Method

You can qualify the AS clause with one of these load method hints:

• AUTO
• DIRECT
• TRICKLE

Vertica applies the load hint to the data that is loaded into the new table. You can also specify a load method in the CREATE TABLE statement, which is saved to the table schema. However, the load method applies only to load operations after the table is created.

In the following example, table bar is created and loaded from table foo with a CREATE TABLE AS statement. The CREATE statement is qualified with the load method DIRECT, which is saved to bar's schema definition. The load method specifies that all future load operations are written to ROS. The CREATE statement's AS clause also contains the load method hint /*+DIRECT*/, which specifies to load the data queried from foo into ROS:

=> SELECT * FROM foo;
 col1 | col2 | col3
------+------+------
    4 |    5 |    6
    1 |    2 |    3
(2 rows)

=> CREATE TABLE bar DIRECT AS SELECT /*+DIRECT*/ * FROM foo;
CREATE TABLE
dbadmin=> SELECT EXPORT_TABLES ('','bar');
                                    EXPORT_TABLES
-------------------------------------------------------------------------------------
CREATE TABLE public.bar
(
    col1 int,
    col2 int,
    col3 int
) DIRECT ;
(1 row)

For details, see Choosing a Load Method.

Loading Historical Data

You can qualify a CREATE TABLE AS query with the option, with AT epoch. AT epoch specifies to return historical data, where epoch is one of the following:

• EPOCH LATEST: Return data from the latest committed DML transaction.
• EPOCH integer: Return data from the integer-specified epoch.
• TIME 'timestamp: Return data from the timestamp-specified epoch.

Note: These options are invalid for external tables.

For details, see Historical Queries in Analyzing Data.

Zero-Width Column Handling

If the query returns a column with zero width, Vertica automatically converts it to a VARCHAR(80) column. For example:

=> CREATE TABLE example AS SELECT '' AS X;
CREATE TABLE
=> SELECT EXPORT_TABLES ('', 'example');
                       EXPORT_TABLES
----------------------------------------------------------
CREATE TEMPORARY TABLE public.example
(
    X varchar(80)
);