Thread: Old "Feature" - Copy table ignores column defaults

I see there are a handful of reports of this, but it does seem broken to 
me. If I am copying a table and a column is blank, surely it makes sense 
to substitute the column default from the table? Right now, it is 
substituting 0 for a null column, instead of -99 as I wished, and here I 
was complaining that it was the national weather service's bug.

Re: [HACKERS] Bug or feature? COPY ignores column defaults
http://archives.postgresql.org/pgsql-hackers/1999-01/msg00667.php
http://archives.postgresql.org/pgsql-hackers/1999-01/msg00650.php

Alas, I will have to run perl regex on this awful weather data to make 
it work right.

Tim

Tim Perdue <tim@perdue.net> writes:
> I see there are a handful of reports of this, but it does seem broken to 
> me. If I am copying a table and a column is blank, surely it makes sense 
> to substitute the column default from the table?

There is no such concept as "blank" in SQL.

7.3 does have the ability to COPY a subset of a column's tables and
insert default values for the omitted columns.  But if a column is
provided in the input, there is no clean way to decide that a particular
input value ought to be replaced by a default.
        regards, tom lane

I said:
> 7.3 does have the ability to COPY a subset of a column's tables and
> insert default values for the omitted columns.

s/column's tables/table's columns/

Must be time to go to bed ...
        regards, tom lane