1.28 COPY: Using Other functions to load Data to tables.

COPY: Using Other functions to load Data to tables.

Many times we need to load columns with data from System functions, date Time functions.

Ex: We need to load information like date and time the data was loaded.

We have already seen this in the "Transform data while loading" section.

Here we just provide examples.

Note that we need to use the "COLUMN AS EXPRESSION " Syntax for this.

COPY expressions can use many HP Vertica-supported SQL functions, operators, constants, NULLs, and comments, including these functions:

Date/time

Formatting Functions

String

Null-handling

System information

Example:

sukul1=> CREATE TABLE USER_30_DAY_TABLES.EMPTABLE

sukul1-> (FIRSTNAME VARCHAR(20),

sukul1(> INITIALS CHAR(1),

sukul1(> DAILYWAGE NUMBER(8,2),

sukul1(> CURRUSER VARCHAR(20),

sukul1(> LOAD_DT DATE,

sukul1(> LOAD_TM TIME

sukul1(> );

sukul1=> COPY USER_30_DAY_TABLES.EMPTABLE

sukul1-> (FIRSTNAME,

sukul1(> LASTNAME FILLER VARCHAR(20),

sukul1(> INITIALS AS SUBSTR(LASTNAME,1,1),

sukul1(> DAILYWAGE,

sukul1(> CURRUSER AS CURRENT_USER(),

sukul1(> LOAD_DT AS CURRENT_DATE(),

sukul1(> LOAD_TM AS CURRENT_TIME

sukul1(> )

sukul1-> FROM LOCAL '/home/sukul1/empdet.dat'

sukul1-> DELIMITER '|'

sukul1-> ABORT ON ERROR

sukul1-> DIRECT;

 Rows Loaded

-------------

           3

(1 row)

sukul1=> select * from USER_30_DAY_TABLES.EMPTABLE;

 FIRSTNAME | INITIALS | DAILYWAGE | CURRUSER |  LOAD_DT   |     LOAD_TM

-----------+----------+-----------+----------+------------+-----------------

 SUKUL     | M        |     23.54 | sukul1   | 2015-11-15 | 01:15:24.609867

 RUTUJA    | K        |    234.11 | sukul1   | 2015-11-15 | 01:15:24.609867

 BHANU     | S        |    123.11 | sukul1   | 2015-11-15 | 01:15:24.609867

(3 rows)