electric-sql / electric-sql/electric

Boolean values differ between replication and query paths: "t"/"f" vs "true"/"false"

Open
#4,068 0 comments 1 reaction 0 assignees View on GitHub
triage
Dominant language
TypeScript
Stars
10.4k
Forks
375
Avg merge
3d 1h
Merged PRs (30d)
18

Description

## Description

Boolean column values are represented differently depending on the response path:

- **Replication path** (`changes_only`): booleans are serialized as `"t"` and `"f"`
- **Query path** (`full` and `subset`): booleans are serialized as `"true"` and `"false"`

This inconsistency means clients cannot rely on a single boolean representation across all Electric response types.

## Root cause

The replication path receives column values as-is from Postgres logical replication, which uses the single-character text output format for booleans (`t`/`f`).

The query path uses `column::text` in SQL (via `pg_cast_column_to_text/1` in `querying.ex`), which produces the full words `true`/`false`.

These are both valid Postgres text representations of `bool`, but they differ.

## Reproduction

1. Create a table with a boolean column:
```sql
CREATE TABLE todos (id TEXT PRIMARY KEY, completed BOOLEAN DEFAULT false, title TEXT);
```
2. Insert a row:
```sql
INSERT INTO todos (id, completed, title) VALUES ('1', true, 'test');
```
3. Compare responses:
- `changes_only` response: `"completed": "t"`
- `full` response: `"completed": "true"`
- `subset` response: `"completed": "true"`

## Expected behavior

Boolean values should be represented consistently across all response types (`changes_only`, `full`, `subset`).

## Actual behavior

`changes_only` returns `"t"`/`"f"`, while `full` and `subset` return `"true"`/`"false"`.

## Context

Originally reported as part of #4039. The `char(n)` padding inconsistency was fixed in #4044, but the boolean mismatch was intentionally left out of scope.

The fix in #4044 used `concat(col, '')` for bpchar to avoid affecting booleans — `concat(bool_col, '')` would produce `t`/`f`, which would have made the query path match replication but would be a breaking change for existing clients consuming `"true"`/`"false"` from snapshots.

## Considerations

The fix needs to decide which representation is canonical:
- **`"true"`/`"false"`** (current query path) — more readable, what most clients likely expect
- **`"t"`/`"f"`** (current replication path) — Postgres native text output

If `"true"`/`"false"` is chosen as canonical, the replication path would need to normalize boolean values before storing/serving them. If `"t"`/`"f"` is chosen, the query path SQL would need adjustment (and this would be a breaking change for snapshot consumers).

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.