Skip to content
Coordinator DB
View as Markdown
View as Markdown

csv_with_names ​

Description ​

CSV with a first row of column names. Input matches names by default. Disable csv_match_names to bind fields positionally.

Input and output. Select it with FORMAT csv_with_names. Names ignore ASCII case; use the snake_case name shown here.

The first record contains column names; all later records contain values using the ordinary CSV quoting rules. Input name matching is enabled by default, so a source can reorder fields while retaining their names.

Disable csv_match_names only when the header should be skipped and values bound in target order. Use ignore_unknown_fields when extra named fields are intentionally allowed.

text
SELECT expressions FORMAT csv_with_names;
INSERT INTO table [(columns)] [SETTINGS ...] FORMAT csv_with_names
<data>

Example usage ​

sql
SELECT CAST(7 AS Int64) AS id, 'Ada' AS name FORMAT csv_with_names;
text
"id","name"
7,"Ada"

Reading reordered named fields ​

The input header names the columns in a different order from the table:

sql
CREATE TABLE named_people(id UInt32, name String);
sql
INSERT INTO named_people FORMAT csv_with_names
name,id
Ada,1
Grace,2
sql
SELECT id, name FROM named_people ORDER BY id FORMAT csv;
text
1,"Ada"
2,"Grace"

Format settings ​

Use a statement SETTINGS clause; these options are not persisted by SET.

Setting Type Default Meaning
csv_crlf Bool 0 End each written row with a carriage return and a line feed instead of a line feed.
csv_double_quotes Bool 1 Read a field that starts with a double quote as quoted text; when off, the quote is an ordinary character.
csv_empty_as_default Bool 1 Give an empty unquoted field the column's DEFAULT; when off, read it as empty text.
csv_enum_by_number Bool 0 Read a whole number in an Enum column as the label's number instead of its name.
csv_match_names Bool 1 Match input names to target columns ignoring ASCII case; when off, skip the names header and read in target order.
csv_null_text String \N The unquoted text that stands for NULL when reading and writing; it must stay one untrimmed field under the current CSV settings.
csv_separator Char , The character between fields when reading and writing: one ASCII character that is not a letter, digit, quote, backslash, or line break. Write a tab as \t.
csv_single_quotes Bool 0 Also read a field that starts with a single quote as quoted text.
csv_skip_lines UInt64 0 Ignore this many lines at the start of the input, before any header row.
csv_trim_blanks Bool 1 Drop spaces and tabs around a field when reading; quotes keep the blanks inside them.
csv_uneven_rows Bool 0 Accept rows with too few or too many fields: missing fields take the column's DEFAULT and extra fields are dropped.
ignore_unknown_fields Bool 0 Skip input keys and header names that match no target column instead of failing.
snappy_mode String basic Read Snappy client files as basic (Hadoop blocks) or framed (standard streaming format).