Thread: Null vs empty string
Hi All, 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. :-( Can you please explain how best to accomplish these results. Kind Regards, Keith ______________________________________________ 99main Internet Services http://www.99main.com
On Fri, Dec 03, 2004 at 08:57:24PM -0500, Keith Worthington wrote: > 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/
> > 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