FDELIMITEDPARSER

Parses data using a delimiter character to separate values.

Parses data using a delimiter character to separate values. The fdelimitedparser loads delimited data, storing it in a single-value VMap.

This parser is for use in Flex tables only. All flex parsers store the data as a single VMap in the LONG VARBINAR_raw__ column. If a data row is too large to fit in the column, it is rejected. Vertica supports null values for loading data with NULL-specified columns.

Note

By default, fdelimitedparser treats empty fields as NULL, rather than as an empty string (''). This behavior makes casting easier. Casting a NULL to an integer (NULL::int) is valid, while casting an empty string to an integer (''::int) is not. If required, use the treat_empty_val_as_null parameter to change the default behavior of fdelimitedparser.

Syntax

FDLIMITEDPARSER ( [parameter-name='value'[,...]] )

Parameters

delimiter: Single character delimiter.
Default:|
record_terminator: Single-character record terminator.
Default:\n
trim: Boolean, specifies whether to trim white space from header names and key values.
Default: true
header: Boolean, specifies that a header column exists. The parser uses col### for the column names if you use this parameter but no header exists.
Default:true
omit_empty_keys: Boolean, specifies how the parser handles header keys without values. If omit_empty_keys=true, keys with an empty value in the headerrow are not loaded.
Default: false
reject_on_duplicate: Boolean, specifies whether to ignore duplicate records (false), or to reject duplicates (true). In either case, the load continues.
Default:false
reject_on_empty_key: Boolean, specifies whether to reject any row containing a key without a value.
Default:false
reject_on_materialized_type_error: Boolean, specifies whether to reject any row value for a materialized column that the parser cannot coerce into a compatible data type. See Using flex table parsers.
Default:false
treat_empty_val_as_null: Boolean, specifies that empty fields become NULLs, rather than empty strings ('').
Default: true

Examples

Create a flex table for delimited data:

t=> CREATE FLEX TABLE delim_flex ();
CREATE TABLE

Use the fdelimitedparser to load some delimited data from STDIN, specifying a comma (,) column delimiter:

=> COPY delim_flex FROM STDIN parser fdelimitedparser (delimiter=',');
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> deviceproduct, severity, deviceversion
>> ArcSight, High, 2.4.1
>> \.

You can now query virtual columns in the delim_flex flex table:

=> SELECT deviceproduct, severity, deviceversion from delim_flex;
 deviceproduct | severity | deviceversion
---------------+----------+---------------
 ArcSight      | High     | 2.4.1
(1 row)