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.

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