Обсуждение: [Proposal] Adding TRIM_SPACE option to COPY

 

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], 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