The Backslash That Decides Where Your CSV Lands: ksql's \copy vs. COPY
The `\copy` vs `COPY` distinction is a concrete workflow difference that eliminates a recurring friction point: needing elevated database privileges or server access just to get a CSV file onto a developer's laptop. For teams running KingbaseES (a PostgreSQL derivative common in Chinese enterprises), knowing that the backslash moves execution to the client side turns a DBA-gated operation into a self-service one-liner.
Exporting query results to CSV for non-technical teams often means wrestling with server-side file permissions in MySQL. KingbaseES's ksql client sidesteps this entirely with `\copy`, a meta-command that executes locally. The file lands on the developer's machine, not the database server, and any ordinary database user can run it. The command supports full-table dumps, filtered SELECT exports with custom columns, and CSV imports that map specific file columns to table columns while letting defaults handle the rest.
The critical distinction is execution context. `COPY` (no backslash) is a server-side SQL command requiring superuser or `sys_write_server_files` role. `\copy` (with backslash) runs in the ksql process on the client machine. The same table, same path, same options produce radically different results depending on that single character. The error message is explicit: anyone can use `\copy`.
For daily workflows — handing a CSV to operations, ingesting a batch file from a partner — the pattern `\copy (SELECT ...) TO 'file' WITH (FORMAT CSV, HEADER true)` replaces temporary tables and server round-trips. An encoding option handles Windows GBK files without pre-conversion.
The `\copy` / `COPY` split is a clean separation of concerns: client-side for ad-hoc data tasks by developers, server-side for DBA-managed bulk operations. The backslash encodes who runs the command and whose filesystem permissions matter.
MySQL's `SELECT INTO OUTFILE` and `LOAD DATA INFILE` default to server-side execution, making the `LOCAL` keyword an opt-in client-side escape hatch. KingbaseES inverts this: `\copy` is client-side by default, and server-side `COPY` is the restricted variant. The ergonomics favor the common case — a developer on their own machine.
Encoding handling is a small detail that prevents a large headache in Chinese enterprise environments, where Excel-generated GBK CSVs are routine. Baking `ENCODING` into the `WITH` clause avoids a separate transcoding step that non-technical staff won't perform.