COPY FROM / COPY TO

COPY moves data between NodeDB and a file on the server's filesystem. COPY <array> FROM STDIN streams an array load from the client.

Paths

A path must be absolute and must not contain ... A relative path is refused with 42601. A path with .. is refused with 42501. The path resolves on the server. The client never sends file bytes for a path form.

COPY FROM a file

COPY <name> FROM '<path>' [WITH (FORMAT <fmt> [, DELIMITER '<char>'] [, HEADER <bool>])]

Reads the file at <path> and inserts every record into <name>. The format comes from the WITH clause or from the file extension. A path with an unknown extension and no FORMAT is an error.

-- NDJSON, one JSON object per line. The format comes from the extension.
COPY users FROM '/data/users.ndjson';

-- JSON array: one top-level array of objects.
COPY users FROM '/data/users.json';

-- CSV with a header row. This is the default for FORMAT csv.
COPY orders FROM '/data/orders.csv' WITH (FORMAT csv);

-- CSV without a header row.
COPY orders FROM '/data/orders.csv' WITH (FORMAT csv, HEADER false);

-- CSV with another delimiter.
COPY products FROM '/data/products.tsv' WITH (FORMAT csv, DELIMITER '\t');

Rules:

• The statement needs write permission on the target.
• A file larger than 16 GiB is refused with 54000.
• A row error names the format and the row number.
• Each row loads as an INSERT. The collection's triggers, defaults, and checks apply to each row.
• A collection with joined trigger bodies loads in an implicit transaction outside a transaction block. The rows and the trigger writes commit together or not at all.
• A collection on the timeseries or spatial engine refuses COPY with 0A000. Use ILP or INSERT for timeseries, and INSERT with a WKT or GeoJSON value for spatial.

Arrays from a file

COPY <array> FROM '<path>' loads CSV rows only. Use WITH (FORMAT csv). Any other format is refused with 0A000. The columns are the dimensions in schema order, then the attributes in schema order. The file streams through the same load as COPY <array> FROM STDIN. See Bulk Load for the row checks, error codes, and chunking.

COPY FROM STDIN

COPY <array> FROM STDIN streams rows from the client over the pgwire COPY protocol.

COPY scene FROM STDIN WITH (FORMAT csv);
COPY scene FROM STDIN WITH (FORMAT csv, DELIMITER ';', HEADER);
COPY scene FROM STDIN;

• The target must be an array. See Bulk Load.
• FORMAT is csv or text. The default is text, tab-delimited. HEADER is off unless given, and a bare HEADER turns it on. This differs from the file form, where HEADER defaults to true.
• COPY <collection> FROM STDIN is refused with 0A000. Load a collection with COPY <collection> FROM '<path>'.
• COPY tenant_restore(<id>) FROM STDIN is the restore path. It needs the BACKUP permission on the tenant.

COPY TO

COPY <collection> TO '<path>' [WITH (FORMAT <fmt> [, DELIMITER '<char>'] [, HEADER <bool>])]
COPY (SELECT ...) TO '<path>' [WITH (...)]

Writes every row of the collection, or the rows of the query, to a file at <path> on the server. The format comes from the WITH clause or from the extension, as for COPY FROM. A named collection must exist, or the statement fails with 42P01. The server writes a temporary file <path>.tmp and renames it to <path>, so a reader never sees a partial file. The command tag is COPY <row count>.

-- Export a whole collection as NDJSON
COPY users TO '/exports/users.ndjson';

-- Export as a JSON array
COPY users TO '/exports/users.json';

-- Export as CSV with a header
COPY orders TO '/exports/orders.csv' WITH (FORMAT csv);

-- Export a query
COPY (SELECT id, name, email FROM users WHERE status = 'active')
    TO '/exports/active_users.csv' WITH (FORMAT csv);

COPY TO is non-transactional. The file is written even if the session rolls back later. The query form accepts any SELECT, including joins, CTEs, and WHERE clauses.