FDELIMITEDPARSER

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.

Parameters

delimiter
 CHAR

[Optional] Indicates a single-character delimiter.

Default value:|

 
record_terminator
 CHAR

[Optional] Indicates a single-character record terminator.

Default value: \n 

trim
 BOOLEAN

[Optional] Determines whether to trim white space from header names and key values.

Default value: true

 
header
 BOOLEAN

[Optional] Specifies that a header column exists. The parser uses col### for the column names if you use this parameter but no header exists.

Default value: true

 
omit_empty_keys
 BOOLEAN

[Optional] Indicates 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 value: false

 
reject_on_duplicate
 BOOLEAN

[Optional] Specifies whether to ignore duplicate records (false), or to reject duplicates (true). In either case, the load continues.

Default value: false

 
reject_on_empty_key
 BOOLEAN

[Optional] Specifies whether to reject any row containing a key without a value.

Default value: false

 
reject_on_materialized_type_error
 BOOLEAN

[Optional] Indicates 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 value: false

 
treat_empty_val_as_null
 BOOLEAN

[Optional] Specifies that empty fields become NULLs, rather than empty strings ('').

Default value: true

Examples

  1. Create a flex table for delimited data: 

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

  2. 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)

See Also