[Proposal] Adding TRIM_SPACE option to COPY

HI,

This change seems very useful, I participated in a related project before. After reviewing the patch,
I have a suggestion, would it be better to restrict the change to only "copy from"?

daidewei1970@163.com

From: 河田達也
Date: 2025-11-25 00:17
To: pgsql-hackers
Subject: [Proposal] Adding TRIM_SPACE option to COPY
Hi,

I'd like to propose adding a new option, TRIM_SPACE, to the COPY command.

Other data warehouse systems such as Snowflake provide similar functionality
(TRIM_SPACE) to improve robustness when loading CSV data. PostgreSQL does not currently have
such an option, although it would be consistent with other user-friendly
features already present in COPY (e.g., FORCE_NULL, FORCE_NOT_NULL, ON_ERROR).

Proposed feature
----------------
Add a boolean option:

TRIM_SPACE = true | false
(default: false)

When enabled, COPY FROM with FORMAT text or csv will trim leading and trailing
ASCII whitespace from each column value before NULL processing and type
conversion. This is applied only to FORMAT text and csv.(not binary)

Example usage:

COPY mytable
FROM '/tmp/data.csv'
WITH (FORMAT csv, HEADER true, TRIM_SPACE true);

This would transform(trim leading and trailing ASCII whitespace):
" AAA ", " BBB", "CCC "

into:
'AAA', 'BBB', 'CCC'
----------------

Thanks in advance for your comments.

Best regards,
Tatsuya Kawata

On Tue, Nov 25, 2025 at 1:18 AM 河田達也 <kawatatatsuya0913@gmail.com> wrote:

Hi,

I'd like to propose adding a new option, TRIM_SPACE, to the COPY command.

Other data warehouse systems such as Snowflake provide similar functionality
(TRIM_SPACE) to improve robustness when loading CSV data. PostgreSQL does not currently have
such an option, although it would be consistent with other user-friendly
features already present in COPY (e.g., FORCE_NULL, FORCE_NOT_NULL, ON_ERROR).

Proposed feature
----------------
Add a boolean option:

TRIM_SPACE = true | false
(default: false)

When enabled, COPY FROM with FORMAT text or csv will trim leading and trailing
ASCII whitespace from each column value before NULL processing and type
conversion. This is applied only to FORMAT text and csv.(not binary)

Example usage:

COPY mytable
FROM '/tmp/data.csv'
WITH (FORMAT csv, HEADER true, TRIM_SPACE true);

This would transform(trim leading and trailing ASCII whitespace):
" AAA ", " BBB", "CCC "

into:
'AAA', 'BBB', 'CCC'
----------------

Thanks in advance for your comments.

I like this idea in general.

Since the docs already notes that leading or trailing spaces in CSV values
can cause errors during loading, at [1]https://www.postgresql.org/docs/devel/sql-copy.html, we'd likely need to update
that section as well if we proceed with this feature.

One question: should TRIM_SPACE remove only the literal space character (' '),
or should it also trim other whitespace characters (e.g., tab, newline,
those recognized by isspace())? I'm not sure how other databases define
this behavior, so it would be good to clarify the intended scope.

Regards,

[1]: https://www.postgresql.org/docs/devel/sql-copy.html

--
Fujii Masao

Fujii Masao <masao.fujii@gmail.com> writes:

On Tue, Nov 25, 2025 at 1:18 AM 河田達也 <kawatatatsuya0913@gmail.com> wrote:

I'd like to propose adding a new option, TRIM_SPACE, to the COPY command.

I like this idea in general.

I'm kind of down on it, because it's inevitably going to add
processing overhead to every COPY operation whether the feature
is used or not. I don't find it likely to be sufficiently
useful to justify that universal cost.

COPY is not a general-purpose filter or ETL tool, and we try
to make it one at our peril.

regards, tom lane

Hi all,

Thank you very much for the helpful feedback so far.
I would like to take a moment to discuss and confirm the overall direction
before preparing the next revision (v2).
My goal is to ensure we are aligned on the intended behavior, scope, and
design considerations for the proposed TRIM_SPACE option.

To summarize the discussion and the points I plan to address in the next
action:

I have a suggestion, would it be better to restrict the change to only

"copy from"?
1. Scope (COPY FROM only)
I agree that the option should apply only to COPY FROM.
I will adjust the patch accordingly.

Since the docs already notes that leading or trailing spaces in CSV values
can cause errors during loading, at [1], we'd likely need to update
that section as well if we proceed with this feature.

2. Documentation
I will update the CSV section mentioned earlier so that it reflects
the behavior when TRIM_SPACE is enabled.

One question: should TRIM_SPACE remove only the literal space character

(' '),

or should it also trim other whitespace characters (e.g., tab, newline,
those recognized by isspace())?

3. Characters to trim
I plan to trim only the ASCII space character (' ') to keep the behavior
simple and avoid ambiguity.
Support for additional whitespace characters could be considered later
if there is consensus.

I'm kind of down on it, because it's inevitably going to add
processing overhead to every COPY operation whether the feature
is used or not. I don't find it likely to be sufficiently
useful to justify that universal cost.

4. Performance / overhead concerns
Thank you for raising this point.
I fully agree that the feature must not introduce overhead when
TRIM_SPACE is disabled.

The trimming logic will be executed only when the option is explicitly
enabled,
so there will be just a single additional conditional check.
While this does technically add some processing overhead, it is expected
to be slight in practice for typical CSV loads and unlikely to be a concern.

COPY is not a general-purpose filter or ETL tool, and we try
to make it one at our peril.

My intention is not to expand COPY into an ETL tool, but rather to
provide a small convenience option similar to FORCE_NULL or ON_ERROR,
to help users avoid common issues caused by unintended leading or
trailing spaces in CSV/text files.

I look forward to your thoughts and any additional feedback before
preparing the next patch.

Thank you again for all the valuable comments.

Best regards,
Tatsuya Kawata

On Tue, Nov 25, 2025 at 10:48 PM 河田達也 <kawatatatsuya0913@gmail.com> wrote:

One question: should TRIM_SPACE remove only the literal space character (' '),
or should it also trim other whitespace characters (e.g., tab, newline,
those recognized by isspace())?

3. Characters to trim
I plan to trim only the ASCII space character (' ') to keep the behavior simple and avoid ambiguity.
Support for additional whitespace characters could be considered later if there is consensus.

Understood.

I'm kind of down on it, because it's inevitably going to add
processing overhead to every COPY operation whether the feature
is used or not. I don't find it likely to be sufficiently
useful to justify that universal cost.

4. Performance / overhead concerns
Thank you for raising this point.
I fully agree that the feature must not introduce overhead when TRIM_SPACE is disabled.

The trimming logic will be executed only when the option is explicitly enabled,
so there will be just a single additional conditional check.
While this does technically add some processing overhead, it is expected to be slight in practice for typical CSV loads and unlikely to be a concern.

I also agree that this feature probably won't add noticeable overhead to
COPY when it isn't used, but it would still be good to measure
the performance impact of the patch.

COPY is not a general-purpose filter or ETL tool, and we try
to make it one at our peril.

My intention is not to expand COPY into an ETL tool, but rather to
provide a small convenience option similar to FORCE_NULL or ON_ERROR,
to help users avoid common issues caused by unintended leading or
trailing spaces in CSV/text files.

I see Tom's point. There might be many possible features that could process
COPY input/output, and we can't reasonably add all of them to PostgreSQL core.
So the question is which ones belong in core. On second thought,
perhaps features that are difficult or impossible to implement outside the core
are the ones that can be considered for COPY itself. Otherwise, it might be
better to avoid expanding COPY unnecessarily. Anyway I'd like to hear more
opinons on this.

As for TRIM_SPACE, it seems possible to implement it as an external module
and call it via COPY PROGRAM. Is this true?

Regards,

--
Fujii Masao

Hi all,

Thank you for the feedback and for taking the time to review this patch.

## Performance Measurement Results

I also agree that this feature probably won't add noticeable overhead to
COPY when it isn't used, but it would still be good to measure
the performance impact of the patch.

I conducted simple performance measurements with 5 million rows to assess
the overhead
when TRIM_SPACE is not used:

**BEFORE patch (master branch):**
Time: 5565.584 ms (00:05.566)
Time: 5626.593 ms (00:05.627)

**AFTER patch (TRIM_SPACE disabled):**
Time: 5840.472 ms (00:05.840)
Time: 5523.806 ms (00:05.524)

The overhead appears to be approximately 1.5%, which seems to be within
an acceptable range for typical COPY operations.

## Regarding COPY PROGRAM as an Alternative

I understand Tom's concern about adding features to core that could be
implemented externally.

As for TRIM_SPACE, it seems possible to implement it as an external module
and call it via COPY PROGRAM. Is this true?

You're right that TRIM_SPACE can technically be
achieved using COPY PROGRAM with an external script. I'd like to share
some observations about this approach:

**Example implementation with COPY PROGRAM:**

```python
#!/usr/bin/env python3
import sys
import csv

reader = csv.reader(sys.stdin)
writer = csv.writer(sys.stdout)

for row in reader:
trimmed_row = [field.strip() for field in row]
writer.writerow(trimmed_row)
```

```sql
COPY users FROM PROGRAM 'python3 /tmp/trim_csv.py'
WITH (FORMAT csv, HEADER true);
```

**Some considerations:**

While the COPY PROGRAM approach works, it does have some practical
limitations:
it requires elevated privileges (`pg_execute_server_program`), may not be
available on all platforms (particularly Windows), and requires users to
handle parsing correctly. That said, I understand the concern about
where to draw the line for features in core, especially given that options
like FORCE_NULL follow a similar pattern.

## My Thoughts

So the question is which ones belong in core. On second thought,
perhaps features that are difficult or impossible to implement outside

the core

are the ones that can be considered for COPY itself. Otherwise, it might

be

better to avoid expanding COPY unnecessarily. Anyway I'd like to hear more
opinons on this.

I can see the validity of the concern about feature creep.
I'm open to the community's perspective on whether this
belongs in core.

If the general feeling is that this functionality should remain external,
I'm happy to withdraw the patch.

Thank you again for your time and valuable feedback. This discussion has
been
very educational for me.

Best regards,
Tatsuya Kawata