Templates and regular expressions ​

Use regexp when an input line can be described by capture groups. Use template when the input or output follows explicit literal separators and field escaping rules.

Read with a regular expression ​

Declare destination columns in capture order and set regexp_pattern on the INSERT. regexp_skip_unmatched controls whether a nonmatching line can be skipped.

The regexp input format is line-based. Pattern matching and field conversion are separate: a captured string must still satisfy its target column type.

Write a template ​

template_row describes field positions and escaping. This example writes two raw fields separated by a vertical bar:

SELECT 7 AS id, 'Ada' AS name
SETTINGS template_row = '${0:Raw}|${1:Raw}'
FORMAT template;

7|Ada

There is no trailing newline. template_row_separator separates rows only; the template's literal bytes determine the rest of the output.

Input matching ​

Template input must match its literal separators. Choose escaping rules that distinguish field content from those separators. All options are statement-scoped.

See regexp and template for complete input syntax and settings.