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. You can use this parser to load data into columnar and flex tables.

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 `header`row 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)