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.
FILLER parameter helps you accomplish these tasks:
- Ignore input columns from the source data.
Transform source data before loading it into the target table.
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
The name of the filler column must not match the name of any column in the target table.
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.
COPY statement reads all of the source data, but only loads the source columns
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
=> 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.