1.22 COPY : Indicating NULLs while loading Fixed width files.

COPY : Indicating NULLs while loading Fixed width files.

The default NULL string for a fixed-width load cannot be an empty string, and instead, consists of all spaces.

The number of spaces depends on the column width declared with the COLSIZES (integer, [,...]) option.

For fixed-width loads, the NULL definition depends on whether you specify NULL at the column or statement level, as follows:

• Statement level—NULL must be defined as a single-character. The default (or custom) NULL character is repeated for the entire width of the column.

• Column Level—NULL must be defined as a string whose length matches the column width.

To turn off NULLs, use the NULL AS option and specify NULL AS ''.

Following is an example of specifying NULL character at the statement level.

Note that when specifying the NULL character at the statement level it should be repeated for the entire length of the column.

<> /home/sukul1 $ vi employeedet3.dat

"employeedet3.dat" 3 lines, 117 characters

RAHUL,DRAVID       ***2010-01-01100000

SAURAV,GANGULY     1012010-10-01******

SHANE,WARNE        85 **********200000

sukul1=> COPY USER_30_DAY_TABLES.EMPLOYEE_1

NAME,

sukul1-> (

sukul1(> NAME,

sukul1(> AGE,

sukul1(> JOINING_DATE FILLER VARCHAR(10),

sukul1(> JOINING_YEAR AS TO_NUMBER(TO_CHAR(TO_DATE(JOINING_DATE,'YYYY-MM-DD'),'YYYY')),

sukul1(> SALARY

sukul1(> )

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

sukul1-> FIXEDWIDTH COLSIZES(19,3,10,6)

sukul1-> NULL '*'

sukul1-> DIRECT;

 Rows Loaded

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

           3

(1 row)

sukul1=> select * from USER_30_DAY_TABLES.EMPLOYEE_1;

        NAME         | AGE | JOINING_YEAR | SALARY

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

 RAHUL,DRAVID        |     |         2010 | 100000

 SHANE,WARNE         |  85 |              | 200000

 SAURAV,GANGULY      | 101 |         2010 |

(3 rows)

sukul1=>

Note that if the null character does not occupy the entire column length, it would be treated as an error.

Following shows an example of specifying NULL characters at column level.

Note that when specifying NULL character at column level we should match the length of the column width.

<> /home/sukul1 $ cat employeedet3.dat

rahul,dravID       ***2010-01-01100000

SAURAV,GANGULY     ***2010-10-01200000

sukul1=> COPY USER_30_DAY_TABLES.EMPLOYEE_1

sukul1-> (

sukul1(> NAME,

sukul1(> AGE NULL '***',

sukul1(> JOINING_DATE FILLER VARCHAR(10),

sukul1(> JOINING_YEAR AS TO_NUMBER(TO_CHAR(TO_DATE(JOINING_DATE,'YYYY-MM-DD'),'YYYY')),

sukul1(> SALARY

sukul1(> )

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

sukul1-> FIXEDWIDTH COLSIZES(19,3,10,6)

sukul1-> DIRECT;

 Rows Loaded

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

           2

(1 row)

sukul1=> select * FROM USER_30_DAY_TABLES.EMPLOYEE_1;

        NAME         | AGE | JOINING_YEAR | SALARY

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

 SAURAV,GANGULY      |     |         2010 | 200000

 rahul,dravID        |     |         2010 | 100000

(2 rows)

1.17 COPY : How to specify a custom NULL value

COPY : How to define a NULL value.

The default NULL value for COPY is an empty string ('').

You can specify a NULL as any ASCII value in the range E'\001' to E'\177' inclusive (any ASCII character except NUL: E'\000').

You cannot use the same character for both the DELIMITER and NULL options.

When NULL is an empty string (''), use quotes to insert an empty string instead of a NULL.

For example, using NULL " ENCLOSED BY '"':

• 1||3 — Inserts a NULL in the second column.

• 1|""|3 — Inserts an empty string instead of a NULL in the second columns.

A NULL is case-insensitive and must be the only value between the data field delimiters.

For example, if the null string is NULL and the delimiter is the default vertical bar (|):

|NULL| indicates a null value.

| NULL | does not indicate a null value.

Example:

Following shows that file employeedet2.dat has consecutive delimiters(|).

Default NULL string is empty string .i.e consecutive delimiters

<> /home/sukul1 $ cat employeedet2.dat

EMMANUEL|28||10000

ALVARO||2005-12-24|20000

COPY USER_30_DAY_TABLES.EMPLOYEE_1

(

NAME,

AGE,

JOINING_DATE FILLER VARCHAR(10),

JOINING_YEAR AS TO_NUMBER(TO_CHAR(TO_DATE(JOINING_DATE,'YYYY-MM-DD'),'YYYY')),

SALARY

)

FROM LOCAL '/home/sukul1/employeedet2.dat'

DELIMITER '|'

ABORT ON ERROR

DIRECT;

sukul1=> select * from USER_30_DAY_TABLES.EMPLOYEE_1;

   NAME   | AGE | JOINING_YEAR | SALARY

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

 ALVARO   |     |         2005 |  20000

 EMMANUEL |  28 |              |  10000

(2 rows)

Following shows how to specify literal NULL values.

<> /home/sukul1 $ cat employeedet1.dat

SUKUL|NUUUL|2010-12-20|10000

RUTUJA|24|2005-12-24|NUUUL

COPY USER_30_DAY_TABLES.EMPLOYEE_1

(

NAME,

AGE,

JOINING_DATE FILLER VARCHAR(10),

JOINING_YEAR AS TO_NUMBER(TO_CHAR(TO_DATE(JOINING_DATE,'YYYY-MM-DD'),'YYYY')),

SALARY

)

FROM LOCAL '/home/sukul1/employeedet1.dat'

DELIMITER '|'

NULL 'NUUUL'

ABORT ON ERROR

DIRECT;

sukul1=> select * from USER_30_DAY_TABLES.EMPLOYEE_1;

   NAME   | AGE | JOINING_YEAR | SALARY

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

 EMMANUEL |  28 |              |  10000

 RUTUJA   |  24 |         2005 |

 ALVARO   |     |         2005 |  20000

 SUKUL    |     |         2010 |  10000

(4 rows)