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
Nobody has claimed this yet.
Assessment
- Difficulty
- 3/5
- Estimated time
- 1-2 days
- Newbie friendliness
- 55/100
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
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
cockroach start-single-node --insecurepsql "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 withfmtCtx.FormatNode(d), using a context
made withtree.FmtPgwireText(copy_to.go:147,:158).DString.Format(pkg/sql/sem/tree/datum.go:1243) honoursfmtPgwireFormatand 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 areDCollatedStringwith 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
COPYinto a collated column. - #172415: a pg_dump roachtest failure where the dumped schema lost a column's
COLLATEclause (schema, not data). - #176153: CSV
COPY TOdoes not quote values equal to theNULLmarker (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
masterHEAD on 2026-10-18), built
from source withbazel 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), psycopg2copy_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
- No Dockerfile or Docker Compose file
- No pull request template
- Read the contributing guide
First steps
- Read the whole issue, then the project's contributing guide.
- Comment on the issue to say you are picking it up — it saves two people doing the same work.
- Fork the repository and make your change on a branch.
- Open a pull request that references the issue number.
More from cockroachdb/cockroach
-
C-bug O-community X-blathers-untriaged
Difficulty 2/5 1-3 hours Newbie friendliness 76/100
cockroachdb/cockroach#176245 · 1 comment ·
-
sql: pg_database.dattablespace is 0 but pg_tablespace.oid is 1663, so they no longer joinPossibly taken @1AbdullahButt claimed this 14 days ago. OpenC-bug O-community X-blathers-triaged X-move-internal
Difficulty 2/5 1-3 hours Newbie friendliness 68/100
cockroachdb/cockroach#176228 · 1 comment ·
-
PG compat: overflow in integer power calculation triggers incompatible SQLSTATEMay be free again @darshpatel1052 claimed this 18 days ago, and no pull request is open. OpenC-bug O-community X-blathers-untriaged
Difficulty 2/5 1-3 hours Newbie friendliness 62/100
cockroachdb/cockroach#176223 · 2 comments ·
-
util/log: bufferedSinkCloser.Close leaks a goroutine after timeoutPossibly taken @dajiaohuang claimed this 14 days ago. OpenC-bug O-community X-blathers-untriaged
Difficulty 1/5 Under an hour Newbie friendliness 84/100
cockroachdb/cockroach#176210 · 1 comment ·
-
C-bug O-community T-sql-queries X-blathers-triaged X-move-internal
Difficulty 2/5 1-3 hours Newbie friendliness 78/100
cockroachdb/cockroach#176150 · 1 comment ·
All issues in cockroachdb/cockroach
Similar issues
-
area/import-export area/projects concern/ux
Difficulty 2/5 1-3 hours Newbie friendliness 68/100
go-vikunja/vikunja#4455 · 1 comment ·
Maintainers usually reply within 2 days
-
Dependabot PRs no longer merge automaticallyPossibly taken @dwisiswant0 claimed this today. Open
Difficulty 2/5 1-3 hours Newbie friendliness 78/100
projectdiscovery/urlfinder#160 ·
Maintainers usually reply within 1 day
-
database
Difficulty 2/5 1-3 hours Newbie friendliness 72/100
chnm/legal-modernism#450 ·
Maintainers usually reply within 1 day
-
tech-debt
Difficulty 2/5 1-3 hours Newbie friendliness 76/100
Maintainers usually reply within 1 day
-
bug ready-for-triage ready-to-code
Difficulty 2/5 1-3 hours Newbie friendliness 62/100
RedHatInsights/insights-ingress-go#946 · 4 comments ·
Maintainers usually reply within 1 day