PART 4 · EXPORTING AND IMPORTING DATA (CSV)
Bulk Data Transfer: Server-Side COPY and Client-Side \copy
How to export a report for a game designer to CSV or download the initial item catalog in a matter of seconds
The COPY SQL command (executed by the DBMS server)
The \copy metacommand in psql (executed on the client)
Syntax: COPY game.items TO '/tmp/items.csv' CSV HEADER;
• The file path is looked up on the PostgreSQL server's own file system (inside the Docker container).
• Requires superuser privileges for the `postgres` user or the system roles `pg_read_server_files` and `pg_write_server_files`.
Syntax: \copy (SELECT ...) TO 'report.csv' WITH CSV HEADER
• Reads from and writes to a file on the machine where the psql client is running, passing the stream over a network connection.
• Does not require superuser privileges—it even works under the game_readonly role for exporting reports!
-- 1. Выгружаем витрину игроков в CSV-файл от имени аналитика (game_readonly):
\copy (SELECT * FROM game.v_player_summary ORDER BY total_spent DESC) TO '/tmp/player_report.csv' WITH CSV HEADER
-- 2. Загружаем новые предметы из CSV в таблицу game.items (без колонки item_id — её сгенерирует БД!):
\copy game.items(item_code, title, rarity, base_price) FROM '/tmp/new_items.csv' WITH CSV HEADER
-- 3. ВАЖНО: если вы когда-либо загружали CSV с явными ID (OVERRIDING SYSTEM VALUE), синхронизируйте sequence:
SELECT setval(pg_get_serial_sequence('game.items', 'item_id'), MAX(item_id)) FROM game.items;
Why does the sequence break after manually loading an ID? The counter is not aware of manually entered numbers—calling setval(...) resets it to MAX(id).