COPY

Use the COPY command to export query results to standard output in TSV format.

The COPY command allows you to export query results from RisingWave to standard output (STDOUT) in tab-separated values (TSV) format. This is useful for integrating RisingWave with external systems that can consume PostgreSQL-compatible COPY output, such as ClickHouse’s PostgreSQL table engine.

Syntax

COPY ( query ) TO STDOUT;

Parameters

Parameter	Description
query	A SELECT query that specifies the data to export.

Output format

The COPY TO STDOUT command outputs data in TSV format with the following characteristics:

• Fields are separated by tab characters (\t)
• Rows are separated by newline characters (\n)
• NULL values are represented as empty fields
• Special characters are escaped:
  • Tab characters in data are escaped as \t
  • Newline characters in data are escaped as \n
  • Carriage return characters in data are escaped as \r
  • Backslash characters in data are escaped as \\

Examples

Export results from a simple query:

Basic example

COPY (SELECT id, name FROM users ORDER BY id) TO STDOUT;

This outputs data in TSV format:

1	Alice
2	Bob
3	Charlie

When data contains special characters, they are properly escaped:

Example with special characters

CREATE TABLE t (id int, name string);
INSERT INTO t VALUES 
  (1, 'name'), 
  (2, null), 
  (3, E'name1\tname'),
  (4, E'name1\rname'),
  (5, E'name1\nname'),
  (6, E'name1\\name');
FLUSH;

COPY (SELECT name, id FROM t ORDER BY id) TO STDOUT;

The output is:

name	1
	2
name1\tname	3
name1\rname	4
name1\nname	5
name1\\name	6

Integration with ClickHouse

The COPY TO STDOUT command enables ClickHouse to query RisingWave data using the PostgreSQL table engine. ClickHouse uses this syntax internally when loading data from PostgreSQL-compatible databases.

Example ClickHouse configuration:

CREATE TABLE clickhouse_table
ENGINE = PostgreSQL('risingwave_host:4566', 'database_name', 'table_name', 'user', 'password');

ClickHouse will automatically use COPY () TO STDOUT to efficiently load data from RisingWave.

Related topics

• ClickHouse sink — Send data from RisingWave to ClickHouse