Importing and Exporting Fixed Width Data

Importing and Exporting Fixed Width Data

Specify custom formats for fixed-width data with the Greenplum Database
functions fixedwith_in and fixedwidth_out. These
functions already exist in the file
$GPHOME/share/postgresql/cdb_external_extensions.sql. The
following example declares a custom format, then calls the
fixedwidth_in function to format the data.

To load all the fields on a line of fixed with
data, you must load them in their physical order. You must specify the field
length, but cannot specify a starting and ending position. The fields names in
the fixed width arguments must match the order in the field list at the
beginning of the CREATE TABLE command.

Set options for blank and null characters.

Trailing blanks are
trimmed by default. To keep trailing blanks, use the
preserve_blanks=on option.You can reset the trailing blanks
option to the default with the preserve_blanks=off option.

Use the null='null_string_value' option to specify a value for
null characters.

If you specify preserve_blanks=on, you must also
define a value for null characters.

If you specify preserve_blanks=off, null is not
defined, and the field contains only blanks, Greenplum
writes a null to the table. If null is defined, Greenplum writes an empty string to the table.

Use the
line_delim='line_ending' parameter to specify the line
ending character. The following examples cover most cases. The
E specifies an escape string constant.