New Feature: Conditionally filter logical rows in a CSV

[davidklebanoff]

I think the ability to conditionally skip/filter a logical row in a CSV, based on the value of a single field (or even better, multiple fields) could be super useful. The conditional logic could be a simple lisp function that returns a boolean value. If the boolean is false, the row is skipped, if true, it is inserted into the database.

A few scenarios to illustrate the value:

• You're importing a massive table of events, but you only want to import events from from last year. ( year == 2016)
• You're importing a massive table of language translations, but you only want english. (lang = "en")
• You're importing a massive inventory table, but you only want items that are in stock. (count > 0)

I'm happy to discuss the idea further if needed.

[dimitri]

That's a feature I have already been thinking about and I think we should work on it yes. One of the things I want to do here is to enable targeting more than a single table depending on what you find in the data, e.g. target directly the right yearly partition depending on the year field in the file.

[dimitri]

Three related asks that form a single coherent design: #270 (skip rows
matching arbitrary predicates, e.g. comment lines that are not valid CSV),
#451 (route rows from one input file to multiple target tables), and
#510 (discard rows with a field-value predicate before they enter the
pipeline). All three live in the same layer of the pipeline and are best
served by a unified syntax extension to the LOAD CSV, LOAD FIXED, and
LOAD COPY commands.

Design

Two kinds of predicate, distinguished by when they apply in the pipeline:

Pre-parse filter — WHEN $line …

Operates on the raw input line as a text string, before the CSV parser
splits it into fields. This is the right place to skip comment lines, BOM
markers, separator-only rows, or any line that is not valid CSV — the parser
never sees these lines so there is no field-count mismatch or parse error:

LOAD CSV FROM 'data.csv' (order_id, customer_id, type, amount)
  WHEN $line NOT STARTING WITH '#'
   AND $line IS NOT EMPTY
  INTO target.orders (order_id, customer_id, amount)
WITH skip header = 1;

$line is a reserved name that always refers to the raw text line. For
regex predicates, awk-compatible /pattern/ syntax is supported as shorthand
for $line ~ 'pattern', with &&, ||, and ! as boolean operators:

LOAD CSV FROM 'data.csv' (order_id, customer_id, type, amount)
  WHEN /^[^#]/ && ! /^[[:space:]]*$/
  INTO target.orders (order_id, customer_id, amount)
WITH skip header = 1;

Lines that fail the pre-parse filter are counted as "filtered" in the summary —
distinct from "rejected" rows (which fail a type cast).

Post-parse routing — per-INTO WHEN clause

After the CSV parser splits the line into named fields, each INTO clause
carries its own optional WHEN predicate that operates on field values by
name. A row is sent to every INTO whose predicate matches (fan-out).
An INTO with no WHEN is a catch-all that always fires:

LOAD CSV FROM 'export.csv' (order_id, customer_id, type, amount)
  WHEN $line NOT STARTING WITH '#'
  INTO target.sales   (order_id, customer_id, amount) WHEN type = 'sale'
  INTO target.refunds (order_id, amount)              WHEN type = 'refund'
  INTO target.archive (order_id, customer_id, type, amount)
WITH skip header = 1;

The WHEN predicate on an INTO clause supports field comparisons (=,
!=, ~ for regex, IS NULL, IS NOT NULL) combined with AND / OR.
Rows that match no INTO clause are silently dropped and counted as
"filtered". The file is read once; each row is broadcast to every matching
writer. Deduplication is left to the user via WITH on conflict do nothing
or a post-load SQL step.

Backward compatibility

The existing single-table syntax with no WHEN continues to work unchanged —
this is the degenerate case of the new syntax (one INTO, no filters):

LOAD CSV FROM 'data.csv' (col1, col2)
INTO target.table (col1, col2)
WITH ...

---

If you'd like to see this feature prioritised, sponsoring its development is
possible through The Art of PostgreSQL OSS sponsorship
program. Sponsored features get
dedicated implementation time and are tracked to completion; the design above
is already in place and ready to go.