Thread: Bug or feature? COPY ignores column defaults
At release 6.4.2, COPY does not respect column defaults: junk=> create table testbed ( junk-> f1 int4 default 5, junk-> f2 float default 7.34, junk-> f3 datetime default now(), junk-> f4 text default 'default'); CREATE junk=> copy testbed from stdin; Enter info followed by a newline End with a backslash and a period on a line by itself. >> >> \. junk=> select * from testbed; f1|f2|f3|f4 --+--+--+--0| | | (1 row) INSERT works correctly, however. Is this intentional, or a bug? -- Oliver Elphick Oliver.Elphick@lfix.co.uk Isle of Wight http://www.lfix.co.uk/oliver PGP key from public servers; key ID32B8FAA1 ======================================== "Many are the afflictions of the righteous; but the LORD delivereth him out of them all." Psalm 34:19
Oliver Elphick wrote: > > At release 6.4.2, COPY does not respect column defaults: > > INSERT works correctly, however. > > Is this intentional, or a bug? This is standard behaviour. DEFAULT value is sabstituted only if column value (including NULL) is not specified in INSERT statement. Vadim
>
> Oliver Elphick wrote:
> >
> > At release 6.4.2, COPY does not respect column defaults:
> >
> > INSERT works correctly, however.
> >
> > Is this intentional, or a bug?
>
> This is standard behaviour. DEFAULT value is sabstituted
> only if column value (including NULL) is not specified in
> INSERT statement.
And so for the rule system. It is not invoked on COPY, so
rewrite rules don't take effect.
If you want some columns to have defaults assigned when the
value in COPY is NULL, or maybe override something like a
timestamp field, you could define a trigger. Triggers are
called from COPY.
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#======================================== jwieck@debis.com (Jan Wieck) #
Vadim Mikheev wrote: >Oliver Elphick wrote: >> >> At release 6.4.2, COPY does not respect column defaults: >> >> INSERTworks correctly, however. >> >> Is this intentional, or a bug? > >This is standard behaviour. DEFAULT value is substituted>only if column value (including NULL) is not specified in >INSERT statement. Well, isn't that the case here? junk=> copy testbed from stdin; Enter info followed by a newline End with a backslash and a period on a line by itself.>> >> \. I haven't specified \N; there is no value at all for the column, so surely the default should be used? If that is not the case, I will add an explanation to the documentation for COPY. -- Oliver Elphick Oliver.Elphick@lfix.co.uk Isle of Wight http://www.lfix.co.uk/oliver PGP key from public servers; key ID32B8FAA1 ======================================== "Many are the afflictions of the righteous; but the LORD delivereth him out of them all." Psalm 34:19
Vadim Mikheev <vadim@krs.ru> writes:
> Oliver Elphick wrote:
>> At release 6.4.2, COPY does not respect column defaults:
>> INSERT works correctly, however.
>> Is this intentional, or a bug?
> This is standard behaviour.
As it must be, or dumping/reloading a table via COPY would
fail to preserve null fields in columns with defaults.
regards, tom lane
"Oliver Elphick" <olly@lfix.co.uk> writes:
> junk=> copy testbed from stdin;
> Enter info followed by a newline
> End with a backslash and a period on a line by itself.
>>>
>>> \.
> I haven't specified \N; there is no value at all for the column, so
> surely the default should be used?
Oh, I see what you're complaining about. No, that still shouldn't
mean "substitute the default". An empty input means an empty string
for text fields. It MUST NOT mean substitute the default, or you
can't save and reload empty text fields.
I would argue that an empty input field in COPY ought to be a syntax
error for int4 and other types that don't accept an empty string as a
valid external representation. You ought to be getting something much
like the result of
play=> select '':int4;
ERROR: parser: parse error at or near ":"
play=> select '':float;
ERROR: parser: parse error at or near ":"
play=>
(In fact, I'm surprised you're not getting that. Is COPY ignoring
the complaints from the type conversion routines?)
There's a further issue here, which is that (I assume) you just pressed
return and didn't type the three TAB characters that should have been
required as field separators for your four-column table. That should've
been a syntax error too, IMHO.
So, I agree COPY has a bug, but not the one you say ;-)
regards, tom lane
I wrote:
> You ought to be getting something much like the result of
> play=> select '':int4;
> ERROR: parser: parse error at or near ":"
Sheesh, need to learn to count my colons. Of course, what I should've
written was:
play=> select ''::int4;
?column?
-------- 0
(1 row)
which strikes me as being a bug in the INT4 text-to-value conversion
routine: it ought to be griping about bad input. (float4 and float8
also seem overly permissive.)
The other thing COPY is evidently doing is substituting NULLs for
the remaining fields if it hits RETURN before getting the right number
of column separators. I still say that's a bad idea, and that raising
a syntax error would be safer behavior. COPY is not particularly
intended to be user-friendly, it's intended to be a simple and reliable
dump/reload syntax (no?). Allowing omissions in order to ease typing
just makes the behavior less predictable.
regards, tom lane