把 PostgreSQL 內的資料以 JSON lines 格式取出

最近有個需求要把資料從 PostgreSQL 拉出來、並且存成 JSON lines 格式。想說這算是一個常見格式了，所以研究（vibe）了一下能不能偷懶。

這份需求的核心可以透過 row_to_json 達成，顧名思義，它會幫忙把每一列的資料轉換為 JSON 格式。 搭配 psql 來跑指令、拉結果：

COPY (
  SELECT
    row_to_json(t)
  FROM "public"."demo" AS t
)
TO STDOUT
\g '/tmp/output.jsonl'
;

由於拉資料的時候本來就是一列一列寫，所以輸出的檔案原生就是 JSON lines 格式，準備收工。

然後沒有意外地、出意外了——如果內文有出現需要對 JSON 跳脫的字元，例如 \n，那麼輸出的結果會是不合法的 JSON。

而原因發生在 COPY 上，上面的指令中使用了 COPY 指令來讀取資料，並且預設以文字格式（text format） 處理。然而，以文字格式傳輸時伺服器端會自動為反斜線進行跳脫，客戶端自身有義務進行處理：

解決的手段是改用 CSV 格式 傳輸，然後把分隔符號換成比較不會撞到內文的符號：

COPY (
  SELECT
    row_to_json(t)
  FROM "public"."demo" AS t
)
TO STDOUT
WITH (
  FORMAT csv,
  DELIMITER E'\x1f',
  QUOTE E'\x1e'
)
\g '/tmp/data.jsonl'

目前用起來效果不錯 ✨🍰✨