This is the multi-page printable view of this section. Click here to print.
You can create a table from other tables in two ways:
Replicate an existing table through CREATE TABLE...LIKE.
Create a table from a query through CREATE TABLE...AS.
COPY_TABLE.
You can create a table from an existing one using
CREATE TABLE with the LIKE clause:
CREATE TABLE [schema.]table-name LIKE [schema.]existing-table
[ {INCLUDING | EXCLUDING} PROJECTIONS ]
[ {INCLUDE | EXCLUDE} [SCHEMA] PRIVILEGES ]
Creating a table with LIKE replicates the source table definition and any storage policy associated with it. It does not copy table data or expressions on columns.
CREATE TABLE...LIKE copies all table constraints, with the following exceptions:
Foreign key constraints.
Any column that obtains its values from a sequence, including IDENTITY columns. Vertica copies the column values into the new table, but removes the original constraint. For example, the following table definition sets an IDENTITY constraint on column ID:
CREATE TABLE public.Premium_Customer
(
ID IDENTITY ,
lname varchar(25),
fname varchar(25),
store_membership_card int
);
The following CREATE TABLE...LIKE statement replicates this table as All_Customers. Vertica removes the IDENTITY constraint from All_Customers.ID, changing it to an integer column with a NOT NULL constraint:
=> CREATE TABLE All_Customers like Premium_Customer;
CREATE TABLE
=> select export_tables('','All_Customers');
export_tables
---------------------------------------------------
CREATE TABLE public.All_Customers
(
ID int NOT NULL,
lname varchar(25),
fname varchar(25),
store_membership_card int
);
(1 row)
You can qualify the LIKE clause with INCLUDING PROJECTIONS or EXCLUDING PROJECTIONS, which specify whether to copy projections from the source table:
EXCLUDING PROJECTIONS (default): Do not copy projections from the source table.
INCLUDING PROJECTIONS: Copy current projections from the source table. Vertica names the new projections according to Vertica naming conventions, to avoid name conflicts with existing objects.
You can specify default inheritance of schema privileges for the new table:
EXCLUDE [SCHEMA] PRIVILEGES (default) disables inheritance of privileges from the schema
INCLUDE [SCHEMA] PRIVILEGES grants the table the same privileges granted to its schema
For more information see Setting privilege inheritance on tables and views.
The following restrictions apply to the source table:
It cannot have out-of-date projections.
It cannot be a temporary table.
Create the table states:
=> CREATE TABLE states (
state char(2) NOT NULL, bird varchar(20), tree varchar (20), tax float, stateDate char (20))
PARTITION BY state;
Populate the table with data:
INSERT INTO states VALUES ('MA', 'chickadee', 'american_elm', 5.675, '07-04-1620');
INSERT INTO states VALUES ('VT', 'Hermit_Thrasher', 'Sugar_Maple', 6.0, '07-04-1610');
INSERT INTO states VALUES ('NH', 'Purple_Finch', 'White_Birch', 0, '07-04-1615');
INSERT INTO states VALUES ('ME', 'Black_Cap_Chickadee', 'Pine_Tree', 5, '07-04-1615');
INSERT INTO states VALUES ('CT', 'American_Robin', 'White_Oak', 6.35, '07-04-1618');
INSERT INTO states VALUES ('RI', 'Rhode_Island_Red', 'Red_Maple', 5, '07-04-1619');
View the table contents:
=> SELECT * FROM states;
state | bird | tree | tax | stateDate
-------+---------------------+--------------+-------+----------------------
VT | Hermit_Thrasher | Sugar_Maple | 6 | 07-04-1610
CT | American_Robin | White_Oak | 6.35 | 07-04-1618
RI | Rhode_Island_Red | Red_Maple | 5 | 07-04-1619
MA | chickadee | american_elm | 5.675 | 07-04-1620
NH | Purple_Finch | White_Birch | 0 | 07-04-1615
ME | Black_Cap_Chickadee | Pine_Tree | 5 | 07-04-1615
(6 rows
Create a sample projection and refresh:
=> CREATE PROJECTION states_p AS SELECT state FROM states;
=> SELECT START_REFRESH();
Create a table like the states table and include its projections:
=> CREATE TABLE newstates LIKE states INCLUDING PROJECTIONS;
View projections for the two tables. Vertica has copied projections from states to newstates:
=> \dj
List of projections
Schema | Name | Owner | Node | Comment
-------------------------------+-------------------------------------------+---------+------------------+---------
public | newstates_b0 | dbadmin | |
public | newstates_b1 | dbadmin | |
public | newstates_p_b0 | dbadmin | |
public | newstates_p_b1 | dbadmin | |
public | states_b0 | dbadmin | |
public | states_b1 | dbadmin | |
public | states_p_b0 | dbadmin | |
public | states_p_b1 | dbadmin | |
View the table newstates, which shows columns copied from states:
=> SELECT * FROM newstates;
state | bird | tree | tax | stateDate
-------+------+------+-----+-----------
(0 rows)
When you use the CREATE TABLE...LIKE statement, storage policy objects associated with the table are also copied. Data added to the new table use the same labeled storage location as the source table, unless you change the storage policy. For more information, see Working With Storage Locations.
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 ) ]
[ {INCLUDE | EXCLUDE} [SCHEMA] PRIVILEGES ]
AS [ /*+ LABEL */ ] [ 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
...
You can qualify an AS clause with one or both of the following options:
LABEL hint that identifies a statement for profiling and debugging
AT epoch clause to specify that the query return historical data
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.
You can qualify a CREATE TABLE AS query with an AT epoch clause, to specify that the query return historical data, where epoch is one of the following:
EPOCH LATEST: Return data up to but not including the current epoch. The result set includes data from the latest committed DML transaction.
EPOCH integer: Return data up to and including the integer-specified epoch.
TIME 'timestamp': Return data from the timestamp-specified epoch.
See Epochs for additional information about how Vertica uses epochs.
For details, see Historical queries.
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)
);
If you create a temporary table from a query, you must specify ON COMMIT PRESERVE ROWS in order to load the result set into the table. Otherwise, Vertica creates an empty table.
If the query output has expressions other than simple columns, such as constants or functions, you must specify an alias for that expression, or list all columns in the column name list.
You cannot use CREATE TABLE AS SELECT with a SELECT that returns values of complex types. You can, however, use CREATE TABLE LIKE.