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.

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

  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