Exporting to Parquet

Exporting Query Results Back to Parquet

COPY (query) TO 'file' (FORMAT ...) writes any result as Parquet 129 , CSV or JSON, with Parquet options for the codec, row-group size and partitioning. Here a monthly summary per genre, computed straight from the nested Parquet orders and the books.json catalog, becomes a small Parquet file for a dashboard:

475-export.sql: a summary written to Parquet and read backSQL

SET TimeZone = 'UTC';
COPY (
  WITH lines AS (
    SELECT order_ts, status, unnest(items, recursive := true) FROM 'files/orders.parquet'),
  books AS (SELECT unnest(books, recursive := true) FROM 'data/books.json')
  SELECT date_trunc('month', order_ts)::DATE AS month, genre,
         sum(qty)::INTEGER AS units, sum(qty * unit_price)::DECIMAL(10,2) AS gross
  FROM lines JOIN books ON books.id = lines.book_id
  WHERE status <> 'cancelled' GROUP BY ALL ORDER BY month, genre
) TO 'files/genre_month.parquet' (FORMAT parquet, COMPRESSION zstd, ROW_GROUP_SIZE 100_000);
SELECT count(*) AS rows, sum(gross) AS gross, max(month) AS last
FROM 'files/genre_month.parquet';

Output

...
│    96 │    3303427.30 │ 2026-06-01 │

unnest(items, recursive := true) turned each order's list of structs into one row per line with plain book_id, qty and unit_price columns; the same flattened the catalog's books array. The 96 rows total 3,303,427.30, PostgreSQL 1,289 's figure from PostgreSQL 18 for Analytics, and parquet_metadata shows the declared types kept: dates as INT32 days, the decimal as a scaled INT64, genres dictionary-encoded. Cast results to the narrowest exact type before writing, since readers inherit it, and use PARTITION_BY for large outputs. A detached PostgreSQL partition (Partitioning the Sales Mart) can leave the database this way through the postgres extension (The postgres Extension).

MENU