Re: Null vs empty string

> > I am loading data into a PostgreSQL v7.3.6 database running
> > on RedHat Enterprise Linux ES.  This is accomplished by a
> > shell script running the COPY command.  The data is in comma
> > seperated value format.
> >
> > I am experiencing two difficulties.
> >
> > Firstly, when loading a missing (consecutive commas) numeric
> > value (int4) an error message is generated indicating that
> > the string cannot be parsed.  I would prefer that a NULL
> > value be inserted.
> >
> > Secondly, when loading a missing (consecutive commas) string
> > (varchar) an empty string is inserted.  Again, I would prefer
> > that a NULL value be inserted as later on a foreign key
> > constraint complains bitterly about not being able to match
> > the empty string. :-(
>
> Have you tried using NULL AS?
>
> COPY foo FROM stdin WITH DELIMITER ',' NULL AS '';
>
> Would that break anything else for you?  An alternative would be
> to replace the empty fields in your data with \N, the default string
> that represents NULL.
>
> --
> Michael Fuhr
> http://www.fuhr.org/~mfuhr/

Woohoo! :-) Thanks Michael.  That worked great.  I was not aware of the NULL
AS modifier and its affect.  This is exactly what I wanted to happen.
Personally, when I import nothing I want it to stay as nothing not be changed
to an empty string.  I suppose that some would consider it no big deal that
nothing is imported as an empty string and I MIGHT agree except when nothing
fails to load because PostgreSQL refuses to write an empty string into a
numeric column.  (Perfectly reasonable on PostgreSQL's part.)  Thanks again.

Kind Regards,
Keith

______________________________________________
99main Internet Services http://www.99main.com