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)
No comments:
Post a Comment