COPY FROM with NULL

In this article, you will learn how to copy the data from a file into a table with a NULL value.

Overview

NULL means no value. In other words, it does not have any value, not equal to 0, empty string, or spaces. In Oxla, we can specify a different string as the null value in the COPY FROM query.

Syntax

You can define a string with any strings that will replace the null value, as shown in the syntax below:

COPY table_name FROM 'file_path' (NULL 'string')

Examples

Case #1: Show Blank for NULL Value

1) To begin with, create a CSV file called idvals.csv with a null value:

2) In addition, create a table called idqty by specifying the column with an integer data type:

CREATE TABLE idqty (id INTEGER, quantity INTEGER);

3) Execute the COPY FROM query with a NULL option:

COPY idqty FROM idvals (NULL, 'null');

4) A null value from the CSV file will be displayed in a table with an empty row that has no value, as shown below:

+------+----------+
| id   | quantity | 
+------+----------+
|      | 5        |
| 2    | 2        |
| 3    | 2        |
+------+----------+

Case #2: Show String for NULL Value

1) A string is represented with a double quote. In this case, we create a CSV file called idvals.csv with a null value as a string.

2) Create a table called idqty by specifying the column with an integer data type:

CREATE TABLE idqty (id INTEGER, quantity INTEGER);

3) Execute the COPY FROM query with a NULL option:

COPY idqty FROM idvals (NULL, 'null');

4) You can see that a null value from the CSV file will be displayed in a table with “null”:

+------+----------+
| id   | quantity | 
+------+----------+
| null | 5        |
| 2    | 2        |
| 3    | null     |
+------+----------+