Re: [Proposal] Adding TRIM_SPACE option to COPY

On Tue, Nov 25, 2025 at 1:18 AM 河田達也 <kawatatatsuya0913(at)gmail(dot)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