Streaming JSON Lines Into PostgreSQL Using COPY and psql

JSON Lines is the format most event exports and log shippers produce, and PostgreSQL has no JSON Lines reader. What it does have is COPY, which reads a file one line at a time, and a jsonb column that accepts a line of JSON as text. Put the two together and a 36 MB file of 200,000 events loads in under a second. There is one trap that corrupts data silently for some files and loudly for others, and this article is mostly about getting around it, plus what happens when a line is bad and how to turn the loaded documents into ordinary columns afterwards.

The load that works

Create a table with a single jsonb column and \copy the file into it:

CREATE TABLE ev (doc jsonb NOT NULL);
\copy ev FROM 'events.ndjson' (format csv, quote E'\x01', delimiter E'\x02')

The three options matter. format csv selects CSV parsing, where the only special characters are the quote and the delimiter. quote E'\x01' and delimiter E'\x02' replace the defaults (" and ,) with control characters that never appear in a JSON text, so the whole line arrives as one field, unmodified. The result was 200,000 rows and a table of 52 MB, loaded in 0.74 to 0.78 s over three runs. \copy is the psql meta-command that reads the file on the client and sends it over the connection, which is what you want unless the file is on the database server itself.

Note the quote character. A JSON line is full of double quotes. With the default CSV quote, every line would be parsed as a quoted field and fail. Choosing \x01 is the common workaround, and it is safe for JSON because the JSON grammar forbids raw control characters inside strings: they must be written as \u0001.

Why text format corrupts some files

The default format, text, looks simpler and works on most files:

\copy ev FROM 'events.ndjson'

On the event file it loaded all 200,000 rows in 0.65 to 0.75 s, a hair faster than the CSV form. The problem is that text format treats the backslash as an escape character. COPY text format reads \n as a newline, \t as a tab and \\ as a single backslash before the value reaches the jsonb parser. A JSON line that contains a backslash, which is any string with an escaped quote or a Windows path, is rewritten on the way in.

I tested it with one line:

{"path":"C:\\temp\\new","q":"say \"hi\""}

Text format failed:

ERROR:  invalid input syntax for type json
DETAIL:  Token "hi" is invalid.
CONTEXT:  JSON data, line 1: {"path":"C:\temp\new","q":"say "hi...

The \" became a bare " and the JSON ended too early. The \\t in the path shows the other half of the hazard: had the line been valid after the rewrite, the value would have been stored with a tab or newline in place of the characters the producer wrote. A failure like this one is the lucky case, because you learn about it. Some content survives the rewrite and is silently wrong. The CSV form loaded the same line and returned {"q": "say \"hi\"", "path": "C:\\temp\\new"}.

So the rule is: use format csv with the control-character quote and delimiter, always, even if your first file happens to have no backslashes. The next file will.

What happens with a bad line

By default COPY is all or nothing. I wrote a ten line file where line 7 was a truncated object, {"a":, and loaded it:

ERROR:  invalid input syntax for type json
DETAIL:  The input string ended unexpectedly.
CONTEXT:  JSON data, line 1: {"a":
COPY t3, line 7, column doc: "{"a":"

The table had 0 rows afterwards, because the whole statement rolled back. The useful part is COPY t3, line 7, which names the line in the input file. Open it with sed -n 7p file and fix or drop it. For a file with several bad lines you would find them one at a time, so for an unknown file it is faster to validate first and find every bad line in one pass (finding one bad line in a huge JSON Lines file). A quick gate before loading is jq -c . file > /dev/null; echo $?, which exits non-zero at the first parse error. It reported the error on a different line than the bad one (line 9 for a bad line 7), so use it as a yes or no, and use a per-line check to locate the line.

Two other values jsonb refuses even though they are valid JSON text: the escape \u0000 (PostgreSQL cannot store a NUL in text, and jsonb rejects it with unsupported Unicode escape sequence) and, in jsonb only, numbers beyond its range. The json type, which stores the text as is, accepted the same file in 0.50 s because it only checks syntax. If a producer emits \u0000, strip it upstream (sed 's/\\u0000//g') before the load rather than switching to json, which defers the problem to every later query (choosing between json and jsonb in PostgreSQL).

Streaming from a compressed file or a pipe

\copy ... FROM stdin reads whatever psql receives, so the file never has to be uncompressed on disk:

zcat events.ndjson.gz | psql -c "\copy ev FROM stdin (format csv, quote E'\x01', delimiter E'\x02')"

This loaded 200,000 rows in 0.82 s, slightly slower than reading the plain file because of the decompression, and with no temporary copy. The same shape works with curl -s URL | zcat | psql ..., which turns an export endpoint into a table without ever touching the disk. Compressed files are covered in compressing JSON Lines for storage and reading them back. The statement is atomic, so a download that dies half way produces an error and no partial table.

For a server-side file, COPY ev FROM '/path/events.ndjson' (...) skips the network, but needs the file on the server and the pg_read_server_files role. In managed databases that is usually unavailable, which is why \copy is the default recommendation.

Index after loading, not before

I created a GIN index with jsonb_path_ops on the column and ran the same load: it took 1.96 s instead of 0.75 s, because each row's keys had to be added to the index as the rows arrived. The heap was 52 MB and the index 17 MB. For a bulk load, create the table without indexes, load it, then create the index (see indexing JSONB with GIN). For a table that is loaded daily and queried between loads, the index build after each load is still cheaper than maintaining it per row.

Turning documents into columns

A table of one jsonb column is a staging area. Most queries want typed columns, and a CREATE TABLE AS over the staging table does the conversion in a single statement:

CREATE TABLE ev_cols AS
SELECT (doc->>'id')::int           AS id,
       (doc->>'ts')::timestamptz   AS ts,
       doc#>>'{user,plan}'         AS plan,
       doc->>'event'               AS event,
       (doc->>'amount')::numeric   AS amount
FROM ev;

That took 0.28 s for 200,000 rows. ->> returns text and #>> follows a path, so each field is cast explicitly, which also surfaces dirty data as a cast error rather than a silent null (extracting nested JSONB values with paths). A grouped check confirmed 50,000 rows for each of the four event types, and that only buy events carried an amount.

If you need to know which input line a row came from, add a bigserial to the staging table, CREATE TABLE ev_stage (line bigserial, doc jsonb), and name the column in the copy: \copy ev_stage(doc) FROM .... For a single-process COPY the serial values follow file order, and the first rows came out as lines 1 and 2 with ids 1 and 2. That is an observation, not a guarantee, so for lineage that has to hold up, put a line number in the data before it arrives (awk '{print NR "\t" $0}' is a start). The same pipeline reads a file in a few seconds, and the working-with-JSONB queries are in working with JSONB in PostgreSQL.

When COPY is the wrong tool

The file is not JSON Lines. A single JSON array or a pretty-printed document arrives as many partial lines. Convert it first (converting a JSON array to JSON Lines) or load it whole with pg_read_file.

You need to know about every rejected line. COPY aborts on the first bad line unless you add on_error ignore, which exists since PostgreSQL 17. On the ten line file with a truncated line 7 it loaded 9 rows and printed 1 row was skipped due to data type incompatibility; adding log_verbosity verbose made it print the skipped line number and a snippet. That is fine for a feed where losing a line is acceptable. When it is not, validate first and keep the rejects in a file.

You need upserts. COPY only inserts. Load into a staging table and merge with INSERT ... ON CONFLICT from there.

The data is unbounded. For a continuous stream, batch the lines into files or use a queue, and run COPY on each batch. Opening a COPY per event is much slower than a multi-row insert.

The syntax of every option is in the PostgreSQL manual page for COPY, the json types are described in JSON types, and the line format itself is defined at jsonlines.org. To eyeball a line that failed, paste it into the JSON formatter.