sql: `COPY ... TO` writes `STRING COLLATE` and `CITEXT` values as SQL literals (`e'Stra\u00DFe' COLLATE de`), so a COPY round trip stores the literal text

Open
#176,179 2 comments 0 reactions 0 assignees View on GitHub

Nobody has claimed this yet.

Assessment

Difficulty
3/5
Estimated time
1-2 days
Newbie friendliness
55/100
Issue type
Bug
Clarity
Clearly specified
Activity status
Active
Tech stack
go, postgresql
Domain
backend, databases

Research direction

Start with pkg/sql/copy_to.go and the DCollatedString.Format implementation in pkg/sql/sem/tree/datum.go; compare how text and CSV COPY format values with how pgwire encodes them. Check existing COPY tests for collated strings and CITEXT, then add coverage for both formats. Done means COPY TO exports the stored string contents and a round trip preserves them.

Written by the indexing model from the issue text.

Description

C-bug O-community T-sql-foundations X-blathers-triaged X-move-internal

Describe the problem

COPY ... TO STDOUT, in both text and CSV format, prints each STRING COLLATE <locale> or CITEXT value as a SQL
expression instead of the value:

stored value COPY TO (text) COPY TO ... WITH CSV
'Straße' COLLATE de e'Stra\\u00DFe' COLLATE de e'Stra\u00DFe' COLLATE de
'MiXeD'::CITEXT 'MiXeD' COLLATE und_u_ks_level2 'MiXeD' COLLATE und_u_ks_level2

COPY ... FROM reads that text back as the value. A COPY round trip (CockroachDB to CockroachDB, or
CockroachDB to PostgreSQL) therefore replaces every collated or CITEXT value with its SQL source text, and nothing
reports an error. A plain STRING column in the same row is written correctly. SELECT results are also correct.
Tools that read table data with COPY ... TO STDOUT, such as \copy and pg_dump, get the same text.

To Reproduce

  1. cockroach start-single-node --insecure
  2. psql "postgresql://[email protected]:26257/defaultdb?sslmode=disable":
CREATE TABLE t (id INT PRIMARY KEY, s STRING, co STRING COLLATE de, ci CITEXT);
INSERT INTO t VALUES (1, 'Straße', 'Straße' COLLATE de, 'MiXeD');
COPY t TO STDOUT;
COPY t TO STDOUT WITH CSV;
CREATE TABLE t2 (LIKE t);
\copy t TO 't.txt'
\copy t2 FROM 't.txt'
SELECT * FROM t2;

Output on master:

1	Straße	e'Stra\\u00DFe' COLLATE de	'MiXeD' COLLATE und_u_ks_level2
1,Straße,e'Stra\u00DFe' COLLATE de,'MiXeD' COLLATE und_u_ks_level2
CREATE TABLE
COPY 1
COPY 1
 id |   s    |             co             |               ci
----+--------+----------------------------+---------------------------------
  1 | Straße | e'Stra\\u00DFe' COLLATE de | 'MiXeD' COLLATE und_u_ks_level2
(1 row)

The copy t2 holds e'Stra\\u00DFe' COLLATE de instead of Straße, and 'MiXeD' COLLATE und_u_ks_level2
instead of MiXeD. The edge build 74a73764f18b prints the CITEXT locale quoted ('MiXeD' COLLATE "und-u-ks-level2"),
otherwise the output is the same. (The doubled backslash in t2.co is the separate text-format COPY FROM decoding issue for
collated strings. CSV gives e'Stra\u00DFe' COLLATE de.)

Expected behavior

COPY TO writes the contents of the string, as it does for STRING and as PostgreSQL does for collated text and
citext:

1	Straße	Straße	MiXeD

Additional data / screenshots

Cause on master:

  • pkg/sql/copy_to.go:55 (text) and :90 (CSV) format each datum with fmtCtx.FormatNode(d), using a context
    made with tree.FmtPgwireText (copy_to.go:147, :158).
  • DString.Format (pkg/sql/sem/tree/datum.go:1243) honours fmtPgwireFormat and writes the raw string.
  • DCollatedString.Format (pkg/sql/sem/tree/datum.go:1346-1350) ignores the flags and always writes
    lexbase.EncodeSQLString(contents) + " COLLATE " + locale. CITEXT values are DCollatedString with locale
    und_u_ks_level2, so they take the same path.

pgwire result encoding is not affected because it handles *tree.DCollatedString separately and writes only
v.Contents (pkg/sql/pgwire/types.go:200). Writing d.Contents when fmtPgwireFormat is set, as
DString.Format does, would fix COPY TO.

Related but different:

  • #175602: an assertion failure in binary COPY into a collated column.
  • #172415: a pg_dump roachtest failure where the dumped schema lost a column's COLLATE clause (schema, not data).
  • #176153: CSV COPY TO does not quote values equal to the NULL marker (same file, different cause).

EXPORT INTO CSV writes the plain value and is not affected. A pg_dump of the table taken from v26.3.2 contains
the same literal text in its COPY ... FROM stdin data. (Against master, pg_dump 18.6 currently stops earlier with
unknown function: pg_get_sequence_data(), because master reports server_version 18.0.0.)

Environment:

  • CockroachDB version: master at d30c905fff79 (2026-09-23, current public master HEAD on 2026-10-18), built
    from source with bazel build //pkg/cmd/cockroach-short --config=dev, and the edge binary
    v26.4.0-alpha.3-dev-74a73764f18b (built 2026-10-18). Also reproduced on v26.3.2, v26.4.0-beta.2 and v25.4.17.
  • Server OS: Linux (Ubuntu 22.04), single node
  • Client app: psql (PostgreSQL 20devel), psycopg2 copy_expert

Additional context

COPY TO is the bulk-export path that PostgreSQL drivers and tools use (\copy, pg_dump's table data, ETL tools).
A collated or CITEXT column exported this way comes back with every value replaced by SQL source text, and the
load reports success.

Jira issue: CRDB-70526

Dominant language
Go
Stars
32.6k
Forks
4.2k
PR merge metrics
No merged PRs in 30d

Getting set up

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

More from cockroachdb/cockroach

All issues in cockroachdb/cockroach

Similar issues

More Go issues

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.