1.18 COPY : Loading records with lesser number of data columns. Using TRAILING NULLCOLS option

COPY : Loading records with lesser number of data columns. Using TRAILING NULLCOLS option

Following file shows that the 2nd record in the file does not enough number of columns.

<> /home/sukul1 $ cat employeedet1.dat

SUKUL||2010-12-20|10000

RUTUJA|25

Following shows the error we get when we run the copy

sukul1=> COPY USER_30_DAY_TABLES.EMPLOYEE_1

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/employeedet1.dat'

sukul1-> DELIMITER '|'

sukul1-> ABORT ON ERROR

sukul1-> DIRECT;

ERROR 2035:  COPY: Input record 2 has been rejected (Too few columns found)

sukul1=>

sukul1=> select * from USER_30_DAY_TABLES.EMPLOYEE_1;

 NAME | AGE | JOINING_YEAR | SALARY

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

(0 rows)

In above scenario we had chosen the "AbORT ON ERROR" option. That caused the entire statement to be rolled back and even the correct row was rolled back.

In Such cases we could use the TRAILING NULLCOLS option to load NULLS for the missing columns.

Following COPy statement uses the TRAILING NULLCOLS option.

Note that it does not reject the row and loads NULL's for missing columns.

sukul1=> COPY USER_30_DAY_TABLES.EMPLOYEE_1

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/employeedet1.dat'

sukul1-> DELIMITER '|'

sukul1-> TRAILING NULLCOLS

sukul1-> ABORT ON ERROR

sukul1-> DIRECT;

 Rows Loaded

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

           2

(1 row)

sukul1=> select * from USER_30_DAY_TABLES.EMPLOYEE_1;

  NAME  | AGE | JOINING_YEAR | SALARY

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

 RUTUJA |  25 |              |

 SUKUL  |     |         2010 |  10000

(2 rows)

Caution: This option cannot be used if the columns are defined as NOT NULL.

COPY would try to insert NULL's in NOT null columns and reject the row.