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 ID 32B8FAA1
                 ========================================
     "Many are the afflictions of the righteous; but the 
      LORD delivereth him out of them all."             
                                        Psalm 34:19 



Thu, 28 Jan 1999 13:28:33 +0000
"Oliver Elphick" <olly@lfix.co.uk>
Bug or feature? COPY ignores column defaults
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

Thu, 28 Jan 1999 21:11:51 +0700
Vadim Mikheev <vadim@krs.ru>
Re: [HACKERS] Bug or feature? COPY ignores column defaults
>
> 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) #



Thu, 28 Jan 1999 15:37:03 +0100 (MET)
jwieck@debis.com (Jan Wieck)
Re: [HACKERS] Bug or feature? COPY ignores column defaults
Vadim Mikheev wrote:
  >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 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 ID 32B8FAA1
                 ========================================
     "Many are the afflictions of the righteous; but the 
      LORD delivereth him out of them all."             
                                        Psalm 34:19 



Thu, 28 Jan 1999 15:07:33 +0000
"Oliver Elphick" <olly@lfix.co.uk>
Re: [HACKERS] Bug or feature? COPY ignores column defaults 
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

Thu, 28 Jan 1999 10:41:45 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] Bug or feature? COPY ignores column defaults 
"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

Thu, 28 Jan 1999 14:48:37 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] Bug or feature? COPY ignores column defaults 
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

Thu, 28 Jan 1999 15:04:58 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] Bug or feature? COPY ignores column defaults 