Interpreting Last Column End of Row Values

When bulk-loading delimited text data using the default parser (DELIMITED), the last column end of row value can be any of the following:

Note: The FIXEDWIDTH parser always requires exactly a record terminator. No other permutations work.

For example, given a three-column table, the following input rows for a COPY statement using a comma (,) delimiter are each valid:

1,1,11
1,1,
1,1,,

The following examples illustrate how COPY can interpret different last column end of data row values.

Using a Single End of Row Definition

To see how COPY interprets a single end of row definition:

  1. Create a two-column table two_col, specifying column b with a default value of 5:

    => CREATE TABLE two_col (a int, b int DEFAULT 5);
    CREATE TABLE
    
  2. COPY the two_col table using a comma (,) delimiter, and enter values for only one column (as a single, multi-line entry):

    => COPY two_col from stdin delimiter ',';
    Enter data to be copied followed by a newline.
    End with a backslash and a period on a line by itself.
    >> 1,
    >> 1,
    >> \.
    

    The COPY statement complete successfully.

  3. Query table two_col, to display the two NULL values for column b as blank:

    => SELECT * FROM two_col;
     a | b 
    ---+---
     1 |  
     1 |  
    (2 rows)
    

Here, COPY expects two values for each column, but gets only one. Each input value is followed by a delimiter (,), and an implicit record terminator (a newline character, \n). You supply a record terminator with the ENTER or RETURN key. This character is not represented on the screen.

In this case, the delimiter (,) and record terminator ( \n) are handled independently. COPY interprets the delimiter (,) to indicate the end of one value, and the record terminator (\n) to specify the end of the column row. Since no value follows the delimiter, COPY supplies an empty string before the record terminator. By default, the empty string signifies a NULL, which is a valid column value.

Using a Delimiter and Record Terminator End of Row Definition

To use a delimiter and record terminator together as an end of row definition:

  1. Copy column a (a) of the two_col table, using a comma delimiter again, and enter two values:

    => COPY two_col (a) FROM STDIN delimiter ',';
    Enter data to be copied followed by a newline.
    End with a backslash and a period on a line by itself.
    >> 2,
    >> 2,
    >> \.
    

    The COPY statement again completes successfully.

  2. Query table two_col to see that column b now includes two rows with its default value (5):

    => SELECT * FROM two_col;
     a | b 
    ---+---
     1 |  
     1 |  
     2 | 5
     2 | 5
    (4 rows)
    

In this example, COPY expects values for only one column, because of the column (a) directive. As such, COPY interprets the delimiter and record terminator together as a single, valid, last column end of row definition. Before parsing incoming data, COPY populates column b with its default value, because the table definition has two columns and the COPY statement supplies only one. This example populates the second column with its default column list value, while the previous example used the supplied input data.