Manipulating Source Data Columns

When loading data, your source data might contain one or more columns that do not exist in the target table. Or, the source and target tables have matched columns, but you want to omit one or more source columns from the target table.

The COPY command's FILLER parameter helps you accomplish these tasks: 

• Ignore input columns from the source data.

• Transform source data before loading it into the target table.

The FILLER parameter identifies a column of source data that the COPY command can ignore, or use to compute new values that are loaded into the target table. Define the FILLER parameter data type so it is compatible with the source data. For example, be sure to define a VARCHAR so its length can contain all source data; otherwise, data could be truncated. You can specify multiple filler columns, where each filler column is specified by its own FILLER parameter.

Use FILLER to Ignore Some Values and Compute New Values for the Target Table

Using SQL operators or functions, you can combine two or more source columns into one column; you can also split one column into multiple columns—for example, split date strings into day, month, and year components and load these into target table columns.

The following COPY statement reads all of the source data, but only loads the source columns first_name and last_name. It constructs the data for full_name by concatenating each of the source data columns. To do this, use the FILLER parameter to ignore the middle_name column on load, but use the column when concatenating data to populate the full_name column.

=> CREATE TABLE names(first_name VARCHAR(20), last_name VARCHAR(20), full_name VARCHAR(60));
CREATE TABLE
=> COPY names(first_name,
              middle_name FILLER VARCHAR(20),
              last_name,
              full_name AS first_name||' '||middle_name||' '||last_name)
      FROM STDIN;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> Marc|Gregory|Smith
>> Sue|Lucia|Temp
>> Jon|Pete|Hamilton
>> \.
=> SELECT * FROM names;
 first_name | last_name |     full_name
------------+-----------+--------------------
 Jon        | Hamilton  | Jon Pete Hamilton
 Marc       | Smith     | Marc Gregory Smith
 Sue        | Temp      | Sue Lucia Temp
(3 rows)

If the source field's data type is VARCHAR, be sure to set the VARCHAR length to ensure that the combined length of all FILLER source fields does not exceed the target column's defined length; otherwise, the COPY command could return with an error.