Bulk insert CSV with JSONB field

[steverweber]

Environment

• PostgreSQL version: 17.2
• PostgREST version: docker postgrest/postgrest:v12.2.8
• Operating system: ubuntu24.04

Description of issue

How to insert JSON data using CSV.

When using JSON for bulk insert it fails on my system because memory usage is too high.

cat 500mb_json_array.json  | curl http://localhost:3000/test_raw -X POST \
    -H "Authorization: Bearer $jwt" \
    -H "Content-Type: application/json" \
    -H "Transfer-Encoding: chunked" \
    -H "Prefer: resolution=merge-duplicates" \
    --data-binary @-
# Fails because PostgREST runs out of system memory (12GB)

So would like to try CSV data for bulk insert. Perhaps because this does not require reading the full file like in JSON for the silly matching [ .. ] it might work without memory issues.

• A minimal SQL definition.

create table if not exists public.test_raw (
  id text unique not null,
  data jsonb,
  ts timestamptz not null default now()
);

• POST to PostgREST to add data.

curl --fail-with-body "http://localhost:3000/test_raw?on_conflict=id" -X POST \
    -H "Authorization: Bearer $jwt" \
    -H "Prefer: resolution=merge-duplicates" \
    -H "Content-Type: text/csv" \
    --data-binary @- <<'EOF'
id,data
1,{"a":213}
EOF

• The PostgREST response.

{"code":"PGRST102","details":null,"hint":null,"message":"parse error (Failed reading: satisfy) at \"\\\"a\\\"=213}\""}

Whats the correct way to post CSV data that has a JSON column?

[wolfgangwalther]

The built-in CSV support is neither very good, nor complete. unfortunately.

Instead of improving it, we opted to support custom mime types: https://docs.postgrest.org/en/v12/references/api/media_type_handlers.html.

But, that's only possible for returning data right now, not for POSTing data. This would also be covered by #2826.

Right now, your only option is to write an RPC, which takes an unnamed text or bytea argument and then do the parsing in PostgreSQL.