# Data formats reference

> TSV, CSV, JSON, YAML row encoding, PATCH semantics, NULL representation, type extensions (.txt/.json/.bin), and binary_encoding options.

- Repository: timescale/tigerfs
- GitHub: https://github.com/timescale/tigerfs
- Human docs: https://grok-wiki.com/public/docs/timescale-tigerfs-60719456a5c3
- Complete Markdown: https://grok-wiki.com/public/docs/timescale-tigerfs-60719456a5c3/llms-full.txt

## Source Files

- `internal/tigerfs/format/json.go`
- `internal/tigerfs/format/csv.go`
- `internal/tigerfs/format/yaml.go`
- `internal/tigerfs/format/convert.go`
- `internal/tigerfs/fs/extensions.go`
- `test/integration/format_test.go`

---

---
title: "Data formats reference"
description: "TSV, CSV, JSON, YAML row encoding, PATCH semantics, NULL representation, type extensions (.txt/.json/.bin), and binary_encoding options."
---

TigerFS serializes PostgreSQL rows through the `format` package and exposes them as mount paths: row-as-file (`123.tsv`, `123.json`), row-as-directory column files (`email.txt`, `avatar.bin`), and pipeline exports under `.export/`. Reads always query the database; writes route through `fs.Operations` and `format.Parse*` helpers with either PATCH (format suffix) or PUT (bare path) semantics.

## Format overview

| Format | Row read path | Row write (PATCH) | Bulk export (default) | Bulk import |
|--------|---------------|-------------------|----------------------|-------------|
| TSV | `table/pk.tsv` or `table/pk/.tsv` | Header line + value line | No header row | Header row required (or `.no-headers/`) |
| CSV | `table/pk.csv` or `table/pk/.csv` | Header line + value line | No header row (RFC 4180 quoting) | Header row required (or `.no-headers/`) |
| JSON | `table/pk.json` or `table/pk/.json` | Object keys = columns | Pretty-printed array | JSON array of objects |
| YAML | `table/pk.yaml` or `table/pk/.yaml` | `key: value` document | Multi-document (`---` per row) | Multi-document YAML |

Default row serialization when no extension is given is TSV. Config key `formats.default_format` (`default_format` in `Config`) accepts `tsv`, `csv`, or `json` (default `tsv`); YAML is selected only by the `.yaml` path suffix.

```text
  PostgreSQL row
        │
        ▼
  format.RowTo* / format.RowsTo*     ← read (export, row file, column text)
        │
        ▼
  mount path bytes

  mount write bytes
        │
        ▼
  format.Parse* / parseWriteData
        │
        ▼
  db.UpdateRow / InsertRow / Import*
```

## Row-as-file encoding

### TSV (default)

**Read:** One line, tab-separated values in **schema column order**, trailing newline, **no header row**. NULL → empty field (consecutive tabs for adjacent NULLs).

**Write (`.tsv` suffix, PATCH):** Exactly two lines after trimming trailing empty lines: line 1 = tab-separated column names, line 2 = tab-separated values. Only columns listed in the header are updated. More than two non-empty lines returns a parse error.

**Bare path (no extension, PUT):** Single line of tab-separated values in full schema column order (all columns). Used for paths like `table/123` without a format suffix; new row inserts require a format suffix on macOS NFS.

### CSV

Same layout as TSV but comma-delimited and **RFC 4180** quoting: fields containing `,`, `"`, or newlines are double-quoted; internal quotes are doubled.

**Read:** Headerless, schema order, NULL = empty field.

**Write (`.csv`, PATCH):** Header row + value row via `csv.Reader` (max two records).

### JSON

**Read:** Single compact object per row (`json.Encoder` with `SetEscapeHTML(false)`). NULL → JSON `null`. Booleans are JSON `true`/`false` (not PostgreSQL `t`/`f`). Numbers and strings use JSON-native types where possible (`NormalizeForJSON`).

**Multi-row export** (`.export/json`): Indented JSON array (`SetIndent("", "  ")`), one object per row.

**Write (`.json`, PATCH):** Any JSON object; only keys present are updated. Omitted keys are left unchanged (not set to NULL). Explicit `null` sets NULL.

### YAML

**Read:** Document starts with `---`, then `column: value` lines per row. NULL → literal `null`. Scalars use `ConvertValueToText` then YAML quoting rules (reserved words like `null`, `true`, special characters, leading/trailing space).

**Multi-row export:** Each row is a separate document concatenated (each begins with `---`).

**Write (`.yaml`, PATCH):** Parsed as a single mapping; keys determine updated columns (same PATCH model as JSON).

## PATCH vs PUT write semantics

<Note>
All paths with an explicit format extension (`.tsv`, `.csv`, `.json`, `.yaml`) use **PATCH**: only named columns change; others keep their stored values.
</Note>

| Path pattern | Semantics | Parser |
|--------------|-----------|--------|
| `table/pk.json`, `table/pk.yaml` | PATCH | `ParseJSON`, `ParseYAML` |
| `table/pk.tsv`, `table/pk.csv` | PATCH | `ParseTSVWithHeader`, `ParseCSVWithHeader` |
| `table/pk` (no extension) | PUT — all columns in schema order | `ParseTSV` + schema column list |

Empty body with a format suffix is valid for insert paths (row created with PK from path and column defaults).

New rows via bare path without a format suffix are rejected (`newBarePathRejection`) to avoid NFS inode conflicts; use `.tsv`, `.json`, etc. PK columns from the path are merged into INSERT when omitted from the body.

**Example (PATCH JSON):**

```bash
echo '{"email":"new@example.com"}' > /mount/users/123.json
# UPDATE users SET email = ... WHERE id = 123; name unchanged
```

**Example (PATCH TSV):**

```bash
printf 'email\tname\nnew@example.com\tNew Name\n' > /mount/users/123.tsv
```

## NULL representation

| Surface | NULL on read | NULL on write | Notes |
|---------|--------------|---------------|-------|
| TSV / CSV row or export | Empty field | Empty field in value row | `,,` or `\t\t` between values |
| JSON row / export | `null` | `null` or omitted key | Omitted key on PATCH does **not** clear the column |
| YAML row / export | `null` | `null` | |
| Column file | 0-byte file | `rm column` sets NULL | Writing `""` stores empty string, not NULL |
| Bulk import | Empty field / JSON `null` | Same parsers as export | |

## Value conversion (`ConvertValueToText`)

Shared text rules for TSV, CSV, YAML scalars, and column files:

| PostgreSQL type | Text form |
|-----------------|-----------|
| `boolean` | `t` / `f` |
| `timestamp` / `time` | RFC3339Nano |
| `json` / `jsonb` | Compact JSON string |
| Arrays | JSON array string |
| `uuid` | Standard UUID string |
| `numeric` (pgtype) | JSON-marshaled decimal when available |
| `NULL` | Empty string |

JSON row encoding uses `NormalizeForJSON` instead, preserving JSON booleans and numeric types where compatible.

## Column filename extensions

Type-driven extensions map PostgreSQL types to suffixes; parameterized types strip the length suffix first (`varchar(255)` → `.txt`).

| PostgreSQL type | Extension | Example filename |
|-----------------|-----------|------------------|
| `text`, `varchar`, `char`, `bpchar` | `.txt` | `name.txt` |
| `json`, `jsonb` | `.json` | `metadata.json` |
| `xml` | `.xml` | `config.xml` |
| `bytea` | `.bin` | `avatar.bin` |
| `geometry`, `geography` | `.wkb` | `location.wkb` |
| Integer, numeric, boolean, date, arrays, etc. | (none) | `age`, `tags` |

`FindColumnByFilename` accepts either `column` or `column.ext` when the extension matches the column type. Disable extensions with `--no-filename-extensions` or `no_filename_extensions: true` (exact column names only).

Within a row directory, dot-prefixed format aliases expose the full row: `.json`, `.csv`, `.tsv`, `.yaml` (same encoding as top-level row files).

## `binary_encoding` configuration

<ParamField body="binary_encoding" type="string" default="raw">
BYTEA representation for filesystem I/O. Valid values: `raw`, `base64`, `hex`. Set in `~/.config/tigerfs/config.yaml` under `formats.binary_encoding`, via `TIGERFS_BINARY_ENCODING`, or validated at config load.
</ParamField>

| Value | Intended use |
|-------|----------------|
| `raw` | BYTEA column files contain raw bytes (default; matches current column read/write) |
| `base64` | Config-valid; not wired in `format` or `fs.Operations` read/write paths today |
| `hex` | Config-valid; not wired in `format` or `fs.Operations` read/write paths today |

For binary column files today, read and write pass through byte content directly (`.bin` suffix is a naming hint). Use external tools (`base64`, `xxd`) to transform raw bytes when needed.

<Warning>
Synthesized file-first workspaces use a separate `encoding` column (`utf8` | `base64`) on backing tables in the `tigerfs` schema — not the mount-level `binary_encoding` setting.
</Warning>

## Bulk export and import

### Export (`.export/`)

`readExportFile` serializes pipeline-filtered rows:

| Path | Output |
|------|--------|
| `table/.export/json` | `RowsToJSON` — indented array |
| `table/.export/csv` | `RowsToCSV` — data rows only |
| `table/.export/.with-headers/csv` | `RowsToCSVWithHeaders` |
| `table/.export/tsv` (or default) | `RowsToTSV` or `RowsToTSVWithHeaders` |
| `table/.export/yaml` | `RowsToYAML` — multi-document |

Pipeline context (`.filter/`, `.order/`, `.first/`, `.columns/`, etc.) applies before serialization. Default row cap uses `dir_listing_limit` when no `.first`/`.last` limit is set.

### Import (`.import/`)

| Mode | Path segment | Behavior |
|------|--------------|----------|
| Append | `.import/.append/<fmt>` | Insert only; PK conflict fails |
| Sync | `.import/.sync/<fmt>` | Upsert by primary key |
| Overwrite | `.import/.overwrite/<fmt>` | Delete all rows, then insert |

Parsers: `ParseCSVBulk`, `ParseTSVBulk`, `ParseJSONBulk`, `ParseYAMLBulk` (header/document rules as above). `.import/.../.no-headers/csv` or `.no-headers/tsv` uses schema column order via `ParseCSVBulkNoHeaders` / `ParseTSVBulkNoHeaders`. Default import format when unspecified is `csv`.

## Primary key and path caveats

Text primary keys that end with `.json`, `.csv`, `.tsv`, or `.yaml` are parsed as format extensions (e.g. PK `config` + format `json` for path `config.json`). Integer and UUID keys are unaffected.

Row files (`1.json`) are reachable by direct path but omitted from `ls` at the table level to keep listings small; row directories (`1/`) appear in listings.

## Configuration quick reference

| Key | Env var | Default | Applies to |
|-----|---------|---------|------------|
| `formats.default_format` | `TIGERFS_DEFAULT_FORMAT` | `tsv` | Default row format preference (`tsv`, `csv`, `json`) |
| `formats.binary_encoding` | `TIGERFS_BINARY_ENCODING` | `raw` | BYTEA (`raw`, `base64`, `hex` — see above) |
| `filesystem.no_filename_extensions` | `TIGERFS_NO_FILENAME_EXTENSIONS` | `false` | Column extension mapping |
| `filesystem.trailing_newlines` | `TIGERFS_TRAILING_NEWLINES` | `true` | Column and `.info/count` reads |

## Related pages

<CardGroup>
  <Card title="Data-first exploration" href="/data-first-exploration">
    Row and column reads, PATCH writes, import/export paths, and pipeline pagination.
  </Card>
  <Card title="Capability directories" href="/capability-directories">
    `.export/`, `.import/`, `.columns/`, and pipeline chaining that shapes bulk format output.
  </Card>
  <Card title="Filesystem as API" href="/filesystem-as-api">
    Mount hierarchy, row-as-file vs row-as-directory, and path-to-SQL mapping.
  </Card>
  <Card title="Configuration reference" href="/configuration-reference">
    Full `Config` fields, precedence, and environment variable names.
  </Card>
</CardGroup>