Add support for DEFAULT specification in COPY FROM

From: Israel Barth Rubio <barthisrael(at)gmail(dot)com>
To: pgsql-hackers(at)lists(dot)postgresql(dot)org
Subject: Add support for DEFAULT specification in COPY FROM
Date: 2022-08-16 18:12:20
Message-ID: CAO_rXXAcqesk6DsvioOZ5zmeEmpUN5ktZf-9=9yu+DTr0Xr8Uw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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.

Best regards,
Israel.

Attachment Content-Type Size
v1-0001-Added-support-for-DEFAULT-in-COPY-FROM.patch application/octet-stream 22.6 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Robert Haas 2022-08-16 19:04:57 Re: XLogBeginRead's header comment lies
Previous Message Robert Haas 2022-08-16 17:58:31 XLogBeginRead's header comment lies