Skip to main content

Loading Data

This recipe covers the ways to get rows into ScramDB: single inserts, upserts, and high-throughput bulk loads. Everything you load is visible to analytical queries the instant it commits, so there is no reload step and no waiting for a pipeline to catch up.

We will use one example table throughout:

CREATE TABLE products (
    id          BIGINT PRIMARY KEY,
    sku         VARCHAR(40) NOT NULL,
    name        VARCHAR(200),
    category    VARCHAR(60),
    price       DOUBLE PRECISION,
    updated_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Insert a single row​

INSERT INTO products (id, sku, name, category, price)
VALUES (1, 'SKU-1001', 'Aluminum Bottle', 'kitchen', 19.99);

List the columns you are inserting. Any column you leave out takes its DEFAULT (here, updated_at fills in the current timestamp).

Insert many rows at once​

Batch related rows into one statement. One multi-row INSERT is far faster than many single-row inserts, because the whole batch is written together.

INSERT INTO products (id, sku, name, category, price)
VALUES
    (2, 'SKU-1002', 'Ceramic Mug',    'kitchen',   12.50),
    (3, 'SKU-1003', 'Steel Kettle',   'kitchen',   45.00),
    (4, 'SKU-1004', 'Cotton Apron',   'textiles',  22.00),
    (5, 'SKU-1005', 'Linen Napkins',  'textiles',  16.75);

Insert the results of a query​

Copy rows from one table into another with INSERT ... SELECT. This runs entirely inside the engine, so no data leaves the database.

CREATE TABLE IF NOT EXISTS products_archive (
    id BIGINT, sku VARCHAR(40), name VARCHAR(200), category VARCHAR(60),
    price DOUBLE PRECISION, updated_at TIMESTAMP
);
INSERT INTO products_archive
SELECT * FROM products
WHERE updated_at < DATE '2024-01-01';

An INSERT ... SELECT of any size runs within the server's memory: the query hands its rows to the table batch by batch as it makes them, and the statement still writes all of its rows or none. The query never reads the rows its own statement inserts, so INSERT INTO t SELECT ... FROM t copies t as it was when the statement began, as in PostgreSQL. Limitations lists the statements that still hold their query's whole result first.

Upsert: insert or update on conflict​

When a row might already exist, use ON CONFLICT to decide what happens on a key collision instead of failing.

Skip rows that already exist:

INSERT INTO products (id, sku, name, category, price)
VALUES (2, 'SKU-1002', 'Ceramic Mug', 'kitchen', 12.50)
ON CONFLICT (id) DO NOTHING;

Update the existing row instead. Use excluded to reference the values you tried to insert:

INSERT INTO products (id, sku, name, category, price)
VALUES (2, 'SKU-1002', 'Ceramic Mug', 'kitchen', 13.25)
ON CONFLICT (id) DO UPDATE
    SET price = excluded.price,
        updated_at = CURRENT_TIMESTAMP;

You can guard the update with a condition, so it only fires when it should:

INSERT INTO products (id, sku, name, category, price)
VALUES (2, 'SKU-1002', 'Ceramic Mug', 'kitchen', 13.25)
ON CONFLICT (id) DO UPDATE
    SET price = excluded.price
    WHERE products.price <> excluded.price;

Bulk load with COPY​

For large loads (thousands to millions of rows), COPY is the fast path. It streams the file in batches rather than parsing one statement per row.

Load a CSV that has a header line:

TRUNCATE products;
COPY products FROM '/data/products.csv' WITH (FORMAT CSV, HEADER true);

Choose a different delimiter for pipe- or tab-separated files:

TRUNCATE products;
COPY products FROM '/data/products.psv' WITH (FORMAT CSV, DELIMITER '|', HEADER true);

COPY table FROM '<path>' reads a file on the server. To load a file sitting on your own machine through psql, use the client-side \copy, which takes the same options:

\copy products FROM 'local-products.csv' WITH (FORMAT CSV, HEADER true)

NULLs, defaults, and constraints during COPY​

An empty field in the file becomes NULL only where the column allows it. Load an empty name field into products and you get NULL back, because name is nullable; load an empty sku field and you get an empty string, not NULL and not an error, because sku is NOT NULL. Setting a NULL '<marker>' option doesn't change this: a nullable column's empty field is still read as NULL even when a marker string is configured.

The DEFAULT behavior described above for a single INSERT only covers a column left out of the column list entirely. COPY never substitutes a DEFAULT for a field the file actually supplies, even an empty one; an empty updated_at field loads as NULL (or errors, if the column is NOT NULL), not as the current timestamp.

COPY enforces every constraint the same way INSERT does: NOT NULL, CHECK, domain constraints, PRIMARY KEY, UNIQUE, and FOREIGN KEY. A violation aborts the load rather than skipping the bad row, with the same SQLSTATE an INSERT would report (23505 for a duplicate key, 23503 for a missing parent row). That matters for the products table above: its id BIGINT PRIMARY KEY refuses a duplicate id from the file just as a plain INSERT would, which is why the loads on this page start with TRUNCATE products; when they re-import a file into a table that already holds those ids. Dedupe the source file first if its cleanliness isn't guaranteed; ON CONFLICT is an INSERT clause and does not apply to COPY.

Export with COPY​

COPY also writes data out. Export a whole table or the result of any query:

-- Export a filtered result set
COPY (SELECT id, sku, price FROM products WHERE category = 'kitchen')
    TO '/data/kitchen.csv' WITH (FORMAT CSV, HEADER true);

For moving data between two ScramDB databases, the binary format is the most compact and fastest to reload:

COPY products TO '/data/products.bin' WITH (FORMAT BINARY);
TRUNCATE products;
COPY products FROM '/data/products.bin' WITH (FORMAT BINARY);

Load atomically​

Wrap a multi-step load in a transaction so it either fully lands or not at all. If anything fails, ROLLBACK leaves the table exactly as it was.

BEGIN;
INSERT INTO products (id, sku, name, category, price)
VALUES (6, 'SKU-1006', 'Glass Jar', 'kitchen', 8.40);
INSERT INTO products (id, sku, name, category, price)
VALUES (7, 'SKU-1007', 'Wooden Spoon', 'kitchen', 4.10);
COMMIT;

Tips for fast loads​

  • Prefer COPY over row-by-row INSERT for anything large. It is built for volume and streams the input in batches.
  • Group inserts. One statement with many rows beats many statements with one row each.
  • Create secondary indexes after the bulk load when you can, so the load is not paying to maintain them row by row.
  • Loaded rows are queryable the moment the transaction commits. There is nothing else to refresh.

A large COPY reads the file once and parses it across parallel lanes, so it isn't limited to one core's worth of throughput. The defaults auto-size sensibly for the machine, but if you want to tune the pipeline by hand, the knobs live under [execution] in configuration: copy_batch_rows, copy_pipeline_depth, copy_consumers, and copy_parse_lane_percent.

copy_consumers only changes anything for a file-path COPY of CSV or TSV with no column list, into a table that has no indexes, no foreign keys, no partitioning, and no distributed routing, and only once the file is actually large enough to split into more than one parse lane. Every other shape (a column list, an indexed, foreign-keyed or partitioned table, a small file) loads through the single consumer regardless of the setting. Constraint checking is identical either way: the same checks run whether or not the multi-consumer lane engages.