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
