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