[Monetdb-developers] Did something with Copy Into and nulls change in nightly stable?
I found that the nightly stable build of MonetDB5/sql apparently does not like empty values in tbl text records for importing when using Copy Into, even though the field is defined to allow nulls. This is different behavior compared to the RELEASE version, which did allow this. Please allow me to explain... I have a text file like this: field1|field2|| My db has defined field 1 and 2 as varchar null, and field 3 as INT null. When I run the Copy Into command on the nightly stable, it errors out with this: ' from line 0 field 3 not inserted, expecting type int !SQLException:importTable:failed to import table As a quick test I edited the first line of my text file to insert a fake integer into the 3rd field like this: field1|field2|12345| Then I reran the Copy Into command, this time it errored out on the SECOND line (even those it says line 1 it is really 0 index based so its line 2): ' from line 1 field 3 not inserted, expecting type int !SQLException:importTable:failed to import table So it was happy when I inserted the fake data for the field and does not like rows that are empty. Again, this column is defined as int NULL. Was this change intentional? If not I think there is a bug there. How can I important my text file that has many nulls in it, short of going back to the released version, which I cannot do due to problems with that earlier version. Thank you.
On Fri, Jun 13, 2008 at 09:43:14PM -0700, Rt Ibmer wrote:
I found that the nightly stable build of MonetDB5/sql apparently does not like empty values in tbl text records for importing when using Copy Into, even though the field is defined to allow nulls.
This is different behavior compared to the RELEASE version, which did allow this.
Please allow me to explain...
I have a text file like this: field1|field2||
My db has defined field 1 and 2 as varchar null, and field 3 as INT null.
When I run the Copy Into command on the nightly stable, it errors out with this:
' from line 0 field 3 not inserted, expecting type int !SQLException:importTable:failed to import table
As a quick test I edited the first line of my text file to insert a fake integer into the 3rd field like this: field1|field2|12345|
Then I reran the Copy Into command, this time it errored out on the SECOND line (even those it says line 1 it is really 0 index based so its line 2): ' from line 1 field 3 not inserted, expecting type int !SQLException:importTable:failed to import table
So it was happy when I inserted the fake data for the field and does not like rows that are empty. Again, this column is defined as int NULL.
Was this change intentional? If not I think there is a bug there.
How can I important my text file that has many nulls in it, short of going back to the released version, which I cannot do due to problems with that earlier version. Thank you.
Indeed in the old version we assumed 'emtpy' to mean NULL. This is however not allways what you want. So now we extended the COPY into syntax, ie copy into has a 'NULL as 'your null string'' extension. Niels
------------------------------------------------------------------------- Check out the new SourceForge.net Marketplace. It's the best place to buy or sell services for just about anything Open Source. http://sourceforge.net/services/buy/index.php _______________________________________________ Monetdb-developers mailing list Monetdb-developers@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-developers
-- Niels Nes, Centre for Mathematics and Computer Science (CWI) Kruislaan 413, 1098 SJ Amsterdam, The Netherlands room C0.02, phone ++31 20 592-4098, fax ++31 20 592-4312 url: http://www.cwi.nl/~niels e-mail: Niels.Nes@cwi.nl
participants (2)
-
Niels Nes -
Rt Ibmer