Re: Add support for DEFAULT specification in COPY FROM

On 2022-08-16 Tu 14:12, Israel Barth Rubio wrote:
> Hello all,
>
> With the current implementation of COPY FROM in PostgreSQL we are able to
> load the DEFAULT value/expression of a column if the column is absent
> in the
> list of specified columns. We are not able to explicitly ask that
> PostgreSQL uses
> the DEFAULT value/expression in a column that is being fetched from
> the input
> file, though.
>
> This patch adds support for handling DEFAULT values in COPY FROM. It
> works
> similarly to NULL in COPY FROM: whenever the marker that was set for
> DEFAULT
> value/expression is read from the input stream, it will evaluate the
> DEFAULT
> value/expression of the corresponding column.
>
> I'm currently working as a support engineer, and both me and some
> customers had
> already faced a situation where we missed an implementation like this
> in COPY
> FROM, and had to work around that by using an input file where the
> column which
> has a DEFAULT value/expression was removed.
>
> That does not solve all issues though, as it might be the case that we
> just want a
> DEFAULT value to take place if no other value was set for the column
> in the input
> file, meaning we would like to have a column in the input file that
> sometimes assume
> the DEFAULT value/expression, and sometimes assume an actual given value.
>
> The implementation was performed about one month ago and included all
> regression
> tests regarding the changes that were introduced. It was just rebased
> on top of the
> master branch before submitting this patch, and all tests are still
> succeeding.
>
> The implementation takes advantage of the logic that was already
> implemented to
> handle DEFAULT values for missing columns in COPY FROM. I just
> modified it to
> make it available the DEFAULT values/expressions for all columns
> instead of only
> for the ones that were missing in the specification. I had to change
> the variables
> accordingly, so it would index the correct positions in the new array
> of DEFAULT
> values/expressions.
>
> Besides that, I also copied and pasted most of the checks that are
> performed for the
> NULL feature of COPY FROM, as the DEFAULT behaves somehow similarly.
>
>

Interesting, and probably useful. I've only had a brief look, but it's
important that the default marker not be quoted in CSV mode (c.f. NULL)
-f it is it should be taken as a literal rather than a special value.
Maybe that's taken care of, but there should at least be a test for it,
which I didn't see.

cheers

andrew

--
Andrew Dunstan
EDB: https://www.enterprisedb.com