The Backslash That Decides Where Your CSV Lands: ksql's \copy vs. COPY

Operations wants an order report, product wants a reconciliation table — these tasks often end up on the developer's desk. When exporting query results from MySQL, the first instinct is SELECT ... INTO OUTFILE: but it requires FILE privilege, and the file lands on the database server, invisible from the dev machine, often requiring another login to copy the file out.

KingbaseES offers a smoother approach — ksql's \copy. It's a client-side meta-command executed by the ksql process on the local machine: the file is written directly on the machine running ksql, usable by ordinary accounts without touching server-side permissions.

This article verifies three things about \copy in a KingbaseES V009R001C010 environment: exporting an entire table to CSV, exporting only required columns and rows based on conditions, and importing a CSV back into a table. Finally, a COPY command without the backslash triggers an error, clarifying the exact difference between \copy and COPY. The demo uses an ordinary user app_user connected to the app_db database, with the table app_schema.t_meta_demo.

First, confirm where you're connected

\copy runs within the current session and relies on search_path to find tables when using short table names, so first confirm the location and identity upon connecting:

set search_path to app_schema, public;

select current_database(), current_user, current_schema();

The current database is app_db, the user is app_user, and the first schema in search_path is app_schema. Note that this is an ordinary user — this point becomes critical in section 6, as it is the dividing line where \copy works but server-side COPY does not.

Exporting an entire table

Export the entire t_meta_demo table as a CSV with column headers:

\copy t_meta_demo TO '/tmp/orders_full.csv' WITH (FORMAT CSV, HEADER true);

ksql returns COPY 5, indicating 5 rows were written. cat this file in another terminal:

id,order_no,user_name,status,amount,created_at
1,ORD-20240601-001,alice,paid,1280.50,2026-06-12 14:39:09.209084
2,ORD-20240601-002,bob,pending,360.00,2026-06-12 14:39:09.209084
3,ORD-20240601-003,carol,shipped,899.00,2026-06-12 14:39:09.209084
4,ORD-20240601-004,alice,paid,75.20,2026-06-12 14:39:09.209084
5,ORD-20240601-005,dave,refund,2100.00,2026-06-12 14:39:09.209084

A few points:

• The file path /tmp/orders_full.csv is a local path (on the client machine running ksql), not a path on the database server.
• HEADER true outputs column names in the first row, ready for operations to use directly.
• A full table export includes all fields, including id and created_at.

In MySQL, the equivalent is SELECT * FROM ... INTO OUTFILE '/path/orders.csv': the file is generated on the database server, requires FILE privilege, and is invisible from the dev machine. \copy has none of these restrictions — where the file lands and which account is used are decided by the person running ksql. Also, \copy is a ksql meta-command and must be written on a single line; it cannot be wrapped like ordinary SQL.

Exporting only required columns and rows

Real-world scenarios rarely require exporting an entire table. Operations often wants "orders with status 'paid', only order number, username, and amount." Just follow \copy with a SELECT statement:

\copy (select order_no, user_name, amount from t_meta_demo where status = 'paid') TO '/tmp/orders_paid.csv' WITH (FORMAT CSV, HEADER true);

Returns COPY 2, file content:

order_no,user_name,amount
ORD-20240601-001,alice,1280.50
ORD-20240601-004,alice,75.20

The parentheses contain any SELECT — WHERE, field filtering, ORDER BY, JOIN can all be written. This is the most practical form: no need to create temporary tables; just query what you need and write it directly to a file. CSV column names are taken from the SELECT fields — using aliases applies them, a useful detail for templated exports.

Preparing a CSV for import

Conversely, operations sends a batch of new orders to be added, in CSV format. First, use ! (execute shell command within ksql) to create a file:

! printf 'order_no,user_name,status,amount\nORD-20240602-001,eve,paid,560.00\nORD-20240602-002,frank,pending,120.50\nORD-20240602-003,grace,shipped,3200.00\n' > /tmp/orders_import.csv

cat to confirm content:

order_no,user_name,status,amount
ORD-20240602-001,eve,paid,560.00
ORD-20240602-002,frank,pending,120.50
ORD-20240602-003,grace,shipped,3200.00

This CSV only provides four columns: order_no, user_name, status, amount. It lacks id and created_at — one uses an auto-increment sequence, the other uses a now() default value, so they don't need to be provided in the file.

Importing CSV into a table

When importing, use parentheses to specify which table columns the CSV columns correspond to:

\copy t_meta_demo (order_no, user_name, status, amount) FROM '/tmp/orders_import.csv' WITH (FORMAT CSV, HEADER true);

Returns COPY 3. Check the result (here using expanded mode, fields displayed vertically for better readability):

select * from t_meta_demo order by id;

The original 5 rows plus the newly imported 3 rows total 8 rows. The new data's id is automatically generated by the sequence, and created_at is automatically filled with the import timestamp — these two columns were not provided in the CSV, so they each used their default values.

Two key points:

• Column list parentheses (order_no, user_name, status, amount) : Tells ksql that the data in the CSV fills these columns in order; unlisted columns use default values. Without this parenthesized list, ksql matches the CSV against all table columns in order, and a column count mismatch causes an error.
• HEADER true during import does the opposite of export: During export, it outputs column names in the first row; during import, it tells ksql "the first row is column names, skip it, don't treat it as data." The same option handles one thing in each direction.

In MySQL, the counterpart is LOAD DATA INFILE, which also executes server-side and requires the file to be on the server, unless the LOCAL keyword is added for client-side operation. \copy doesn't care about these distinctions; the file just needs to be readable on the machine where ksql runs.

\copy and COPY differ by just a backslash, but the executor is completely different

Remove the backslash from the previously successful command and run it again with the same app_user:

COPY t_meta_demo TO '/tmp/test_server_copy.csv' WITH (FORMAT CSV, HEADER true);

This time it immediately errors:

ERROR:  must be superuser or a member of the sys_write_server_files role to COPY to a file
HINT:  Anyone can COPY to stdout or from stdin. ksql's \copy command also works for anyone.

Same table, same path and options, but with or without the backslash, the results are worlds apart:

• COPY (without backslash) is an SQL command, executed by the database server. When it writes to a file, the path is a server-side path, requiring a superuser or the sys_write_server_files role. app_user is an ordinary user and is blocked immediately.
• \copy (with backslash) is a ksql meta-command, executed by the ksql process on the local machine. The path is a local client path, using the current operating system user's file permissions, unrelated to database privileges — this is exactly why ordinary users could run all the previous sections successfully.

The error HINT states this relationship plainly: Anyone can COPY to stdout or from stdin. ksql's \copy command also works for anyone. — anyone can use \copy.

Summary

Just remember one thing: look for the backslash.

• \copy: Client-side command, file is on the machine where you run ksql, uses your system account permissions, usable by ordinary database users. Use it for daily data exports for operations or data imports.
• COPY: Server-side command, file is on the database server, requires superuser or sys_write_server_files role. It's for DBAs doing bulk migrations on the server side.

The most convenient command for daily development is \copy (SELECT ...) TO 'file' WITH (FORMAT CSV, HEADER true): choose your own conditions and fields, HEADER true includes column names, one command lands the data operations needs locally.

One final encoding pitfall: if the CSV is exported from Excel on Windows, it's likely GBK encoded. Directly importing or exporting Chinese characters will result in garbled text. Add ENCODING 'GBK' in the WITH clause to specify the file encoding; no need to transcode first.

This is also the most fundamental difference between \copy and MySQL's SELECT INTO OUTFILE / LOAD DATA INFILE: it's not a syntax difference, but which end executes it — one on the client, one on the server. Figure out where the file should land and whose permissions to use, and the choice becomes clear.