[MonetDB-users] Bulk-importing boolean columns
Hi, I need to bulk-import data that was bulk-exported from PostgreSQL. I have only limited control over the raw data. I'm using the following command: COPY INTO table FROM '/path/to/filename' USING DELIMITERS '\t','\n',''; This generally works. The problem is boolean data which is exported by PostgreSQL using the values "t" and "f" (unquoted). MonetDB expects "true" and "false" here. My current work-around is opening the file in Vim and substituting the values (%s/\tt\t/\ttrue\t/g and %s/\tf\t/\tfalse\t/g). Is there a way to make MonetDB accept the original PostgreSQL data? Cheers, Viktor
On 10/17/11 8:25 PM, Viktor Rosenfeld wrote:
Hi,
I need to bulk-import data that was bulk-exported from PostgreSQL. I have only limited control over the raw data.
I'm using the following command:
COPY INTO table FROM '/path/to/filename' USING DELIMITERS '\t','\n','';
This generally works. The problem is boolean data which is exported by PostgreSQL using the values "t" and "f" (unquoted). MonetDB expects "true" and "false" here. My current work-around is opening the file in Vim and substituting the values (%s/\tt\t/\ttrue\t/g and %s/\tf\t/\tfalse\t/g).
Is there a way to make MonetDB accept the original PostgreSQL data? The easiest solution would be to convert the "t" to "true" before it leaves Postgresql. Otherwise, you might use the stream editor "sed" or "awk" to make such trivial changes.
regards, Martin
Cheers, Viktor
------------------------------------------------------------------------------ All the data continuously generated in your IT infrastructure contains a definitive record of customers, application performance, security threats, fraudulent activity and more. Splunk takes this data and makes sense of it. Business sense. IT sense. Common sense. http://p.sf.net/sfu/splunk-d2d-oct _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users
Hi, Martin Kersten wrote:
On 10/17/11 8:25 PM, Viktor Rosenfeld wrote:
Hi,
I need to bulk-import data that was bulk-exported from PostgreSQL. I have only limited control over the raw data.
I'm using the following command:
COPY INTO table FROM '/path/to/filename' USING DELIMITERS '\t','\n','';
This generally works. The problem is boolean data which is exported by PostgreSQL using the values "t" and "f" (unquoted). MonetDB expects "true" and "false" here. My current work-around is opening the file in Vim and substituting the values (%s/\tt\t/\ttrue\t/g and %s/\tf\t/\tfalse\t/g).
Is there a way to make MonetDB accept the original PostgreSQL data? The easiest solution would be to convert the "t" to "true" before it leaves Postgresql.
I can do that for those tables I export myself, but I also have a few that I receive from others.
Otherwise, you might use the stream editor "sed" or "awk" to make such trivial changes.
This would work, but I'm afraid that it changes data it's not supposed to. Cheers, Viktor
On Tue, Oct 18, 2011 at 05:57:23PM +0200, Viktor Rosenfeld wrote:
Hi,
Martin Kersten wrote:
On 10/17/11 8:25 PM, Viktor Rosenfeld wrote:
Hi,
I need to bulk-import data that was bulk-exported from PostgreSQL. I have only limited control over the raw data.
I'm using the following command:
COPY INTO table FROM '/path/to/filename' USING DELIMITERS '\t','\n','';
This generally works. The problem is boolean data which is exported by PostgreSQL using the values "t" and "f" (unquoted). MonetDB expects "true" and "false" here. My current work-around is opening the file in Vim and substituting the values (%s/\tt\t/\ttrue\t/g and %s/\tf\t/\tfalse\t/g).
Is there a way to make MonetDB accept the original PostgreSQL data? The easiest solution would be to convert the "t" to "true" before it leaves Postgresql.
I can do that for those tables I export myself, but I also have a few that I receive from others.
Otherwise, you might use the stream editor "sed" or "awk" to make such trivial changes.
This would work, but I'm afraid that it changes data it's not supposed to.
Well, that depends of course on how well you specify the regular expression that does the matching ;-) Stefan ps: In MonetDB, we try to stick to SQL standards, and though I didn't check, I don't think SQL specifies single letters "t" and "f" as keywords representing boolean values TRUE and FALSE.
Cheers, Viktor
------------------------------------------------------------------------------ All the data continuously generated in your IT infrastructure contains a definitive record of customers, application performance, security threats, fraudulent activity and more. Splunk takes this data and makes sense of it. Business sense. IT sense. Common sense. http://p.sf.net/sfu/splunk-d2d-oct _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users
-- | Stefan.Manegold @ CWI.nl | DB Architectures (INS1) | | http://CWI.nl/~manegold/ | Science Park 123 (L321) | | Tel.: +31 (0)20 592-4212 | 1098 XG Amsterdam (NL) |
On Wed, 19 Oct 2011, Stefan Manegold wrote:
ps: In MonetDB, we try to stick to SQL standards, and though I didn't check, I don't think SQL specifies single letters "t" and "f" as keywords representing boolean values TRUE and FALSE.
<truth value> ::= TRUE | FALSE | UNKNOWN I do wonder if that same SQL92 dictates the use of UNKNOWN keyword why NULL is the only only tri-boolean value accepted then. sql>create table bools (test boolean); operation successful (77.133ms) sql>insert into bools values (unknown); SELECT: identifier 'unknown' unknown sql>insert into bools values (null); 1 affected row (36.447ms) Anyway... this hole deserialisation thing with respect to copy into. Wouldn't it be wise to be able to cast anything 'remotely' parsible into a value to be converted. That said for booleans also accept 0/1 so conversations from MSSQL are also directly possible? It is not that MonetDB behaves like SQLite3 when it is processing, but it would be very nice to reduce the time in 'exact format conversions' for an operation that should be done once, were after, it will be always correct because MonetDB does follow specifications. Stefan
On 10/19/11 10:21 AM, Stefan Manegold wrote:
On Tue, Oct 18, 2011 at 05:57:23PM +0200, Viktor Rosenfeld wrote:
Hi,
Martin Kersten wrote:
On 10/17/11 8:25 PM, Viktor Rosenfeld wrote:
Hi,
I need to bulk-import data that was bulk-exported from PostgreSQL. I have only limited control over the raw data.
I'm using the following command:
COPY INTO table FROM '/path/to/filename' USING DELIMITERS '\t','\n','';
This generally works. The problem is boolean data which is exported by PostgreSQL using the values "t" and "f" (unquoted). MonetDB expects "true" and "false" here. My current work-around is opening the file in Vim and substituting the values (%s/\tt\t/\ttrue\t/g and %s/\tf\t/\tfalse\t/g).
Is there a way to make MonetDB accept the original PostgreSQL data? The easiest solution would be to convert the "t" to "true" before it leaves Postgresql.
I can do that for those tables I export myself, but I also have a few that I receive from others.
Otherwise, you might use the stream editor "sed" or "awk" to make such trivial changes.
This would work, but I'm afraid that it changes data it's not supposed to.
Well, that depends of course on how well you specify the regular expression that does the matching ;-)
Stefan
ps: In MonetDB, we try to stick to SQL standards, and though I didn't check, I don't think SQL specifies single letters "t" and "f" as keywords representing boolean values TRUE and FALSE. Load compatibility is something to consider seriously for takeup. If this is the main difference, then Postgresql load compatibility would be improved by allowing 't'+'f' as strings for type bit in gdk_atoms.
Cheers, Viktor
------------------------------------------------------------------------------ All the data continuously generated in your IT infrastructure contains a definitive record of customers, application performance, security threats, fraudulent activity and more. Splunk takes this data and makes sense of it. Business sense. IT sense. Common sense. http://p.sf.net/sfu/splunk-d2d-oct _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users
On Wed, Oct 19, 2011 at 02:31:02PM +0200, Martin Kersten wrote:
On 10/19/11 10:21 AM, Stefan Manegold wrote:
On Tue, Oct 18, 2011 at 05:57:23PM +0200, Viktor Rosenfeld wrote:
Hi,
Martin Kersten wrote:
On 10/17/11 8:25 PM, Viktor Rosenfeld wrote:
Hi,
I need to bulk-import data that was bulk-exported from PostgreSQL. I have only limited control over the raw data.
I'm using the following command:
COPY INTO table FROM '/path/to/filename' USING DELIMITERS '\t','\n','';
This generally works. The problem is boolean data which is exported by PostgreSQL using the values "t" and "f" (unquoted). MonetDB expects "true" and "false" here. My current work-around is opening the file in Vim and substituting the values (%s/\tt\t/\ttrue\t/g and %s/\tf\t/\tfalse\t/g).
Is there a way to make MonetDB accept the original PostgreSQL data? The easiest solution would be to convert the "t" to "true" before it leaves Postgresql.
I can do that for those tables I export myself, but I also have a few that I receive from others.
Otherwise, you might use the stream editor "sed" or "awk" to make such trivial changes.
This would work, but I'm afraid that it changes data it's not supposed to.
Well, that depends of course on how well you specify the regular expression that does the matching ;-)
Stefan
ps: In MonetDB, we try to stick to SQL standards, and though I didn't check, I don't think SQL specifies single letters "t" and "f" as keywords representing boolean values TRUE and FALSE. Load compatibility is something to consider seriously for takeup. If this is the main difference, then Postgresql load compatibility would be improved by allowing 't'+'f' as strings for type bit in gdk_atoms.
OK. For reference, here's the currect status for both INSERT INTO (incl. implicit type casts) and COPY INTO: ======== $ cat /tmp/bool.sql -------- create table t_bool (i int, s string, b boolean); insert into t_bool values (100, true, true); insert into t_bool values (101, false, false); insert into t_bool values (102, TRUE, TRUE); insert into t_bool values (103, FALSE, FALSE); insert into t_bool values (104, TrUe, TrUe); insert into t_bool values (105, FaLsE, FaLsE); insert into t_bool values (106, t, t); insert into t_bool values (107, f, f); insert into t_bool values (108, T, T); insert into t_bool values (109, F, F); insert into t_bool values (110, 0, 0); insert into t_bool values (111, 1, 1); insert into t_bool values (112, 123, 123); insert into t_bool values (113, -123, -123); insert into t_bool values (114, -123.456, -123.456); insert into t_bool values (115, 'TRUE', 'TRUE'); insert into t_bool values (116, 'FALSE', 'FALSE'); insert into t_bool values (117, 'true', 'true'); insert into t_bool values (118, 'false', 'false'); insert into t_bool values (119, 'TrUe', 'TrUe'); insert into t_bool values (120, 'FaLsE', 'FaLsE'); insert into t_bool values (121, 'T', 'T'); insert into t_bool values (122, 'F', 'F'); insert into t_bool values (123, 't', 't'); insert into t_bool values (124, 'f', 'f'); select * from t_bool; copy 1 records into t_bool from stdin delimiters '\t', '\n', '\''; 200 true true copy 1 records into t_bool from stdin delimiters '\t', '\n', '\''; 201 false false copy 1 records into t_bool from stdin delimiters '\t', '\n', '\''; 202 TRUE TRUE copy 1 records into t_bool from stdin delimiters '\t', '\n', '\''; 203 FALSE FALSE copy 1 records into t_bool from stdin delimiters '\t', '\n', '\''; 204 TrUe TrUe copy 1 records into t_bool from stdin delimiters '\t', '\n', '\''; 205 FaLsE FaLsE copy 1 records into t_bool from stdin delimiters '\t', '\n', '\''; 206 t t copy 1 records into t_bool from stdin delimiters '\t', '\n', '\''; 207 f f copy 1 records into t_bool from stdin delimiters '\t', '\n', '\''; 208 T T copy 1 records into t_bool from stdin delimiters '\t', '\n', '\''; 209 F F copy 1 records into t_bool from stdin delimiters '\t', '\n', '\''; 210 0 0 copy 1 records into t_bool from stdin delimiters '\t', '\n', '\''; 211 1 1 copy 1 records into t_bool from stdin delimiters '\t', '\n', '\''; 212 123 123 copy 1 records into t_bool from stdin delimiters '\t', '\n', '\''; 213 -123 -123 copy 1 records into t_bool from stdin delimiters '\t', '\n', '\''; 214 -123.456 -123.456 copy 1 records into t_bool from stdin delimiters '\t', '\n', '\''; 215 'TRUE' 'TRUE' copy 1 records into t_bool from stdin delimiters '\t', '\n', '\''; 216 'FALSE' 'FALSE' copy 1 records into t_bool from stdin delimiters '\t', '\n', '\''; 217 'true' 'true' copy 1 records into t_bool from stdin delimiters '\t', '\n', '\''; 218 'false' 'false' copy 1 records into t_bool from stdin delimiters '\t', '\n', '\''; 219 'TrUe' 'TrUe' copy 1 records into t_bool from stdin delimiters '\t', '\n', '\''; 220 'FaLsE' 'FaLsE' copy 1 records into t_bool from stdin delimiters '\t', '\n', '\''; 221 'T' 'T' copy 1 records into t_bool from stdin delimiters '\t', '\n', '\''; 222 'F' 'F' copy 1 records into t_bool from stdin delimiters '\t', '\n', '\''; 223 't' 't' copy 1 records into t_bool from stdin delimiters '\t', '\n', '\''; 224 'f' 'f' select * from t_bool; drop table t_bool; ======== ======== $ mclient -i < /tmp/bool.sql -------- operation successful (26.560ms) 1 affected row (8.056ms) 1 affected row (8.205ms) 1 affected row (8.406ms) 1 affected row (8.158ms) 1 affected row (8.109ms) 1 affected row (8.438ms) SELECT: identifier 't' unknown SELECT: identifier 'f' unknown SELECT: identifier 't' unknown SELECT: identifier 'f' unknown 1 affected row (7.822ms) 1 affected row (8.426ms) 1 affected row (8.283ms) 1 affected row (9.342ms) types decimal(6,3) and boolean(1,0) are not equal 1 affected row (6.743ms) 1 affected row (8.403ms) 1 affected row (8.240ms) 1 affected row (8.432ms) 1 affected row (8.481ms) 1 affected row (7.960ms) 1 affected row (8.338ms) 1 affected row (8.971ms) 1 affected row (7.773ms) 1 affected row (8.304ms) +------+-------+-------+ | i | s | b | +======+=======+=======+ | 100 | 1 | true | | 101 | 0 | false | | 102 | 1 | true | | 103 | 0 | false | | 104 | 1 | true | | 105 | 0 | false | | 110 | 0 | false | | 111 | 1 | true | | 112 | 123 | true | | 113 | -123 | true | | 115 | TRUE | null | | 116 | FALSE | null | | 117 | true | true | | 118 | false | false | | 119 | TrUe | null | | 120 | FaLsE | null | | 121 | T | null | | 122 | F | null | | 123 | t | null | | 124 | f | null | +------+-------+-------+ 20 tuples (2.725ms) 1 affected row (126.885ms) 1 affected row (126.912ms) SQLException:importTable:value 'TRUE' from line 1 field 3 not inserted, expecting type boolean failed to import table SQLException:importTable:value 'FALSE' from line 1 field 3 not inserted, expecting type boolean failed to import table SQLException:importTable:value 'TrUe' from line 1 field 3 not inserted, expecting type boolean failed to import table SQLException:importTable:value 'FaLsE' from line 1 field 3 not inserted, expecting type boolean failed to import table SQLException:importTable:value 't' from line 1 field 3 not inserted, expecting type boolean failed to import table SQLException:importTable:value 'f' from line 1 field 3 not inserted, expecting type boolean failed to import table SQLException:importTable:value 'T' from line 1 field 3 not inserted, expecting type boolean failed to import table SQLException:importTable:value 'F' from line 1 field 3 not inserted, expecting type boolean failed to import table 1 affected row (231.489ms) 1 affected row (231.517ms) SQLException:importTable:value '123' from line 1 field 3 not inserted, expecting type boolean failed to import table SQLException:importTable:value '-123' from line 1 field 3 not inserted, expecting type boolean failed to import table SQLException:importTable:value '-123.456' from line 1 field 3 not inserted, expecting type boolean failed to import table SQLException:importTable:value 'TRUE' from line 1 field 3 not inserted, expecting type boolean failed to import table SQLException:importTable:value 'FALSE' from line 1 field 3 not inserted, expecting type boolean failed to import table 1 affected row (305.714ms) 1 affected row (305.759ms) SQLException:importTable:value 'TrUe' from line 1 field 3 not inserted, expecting type boolean failed to import table SQLException:importTable:value 'FaLsE' from line 1 field 3 not inserted, expecting type boolean failed to import table SQLException:importTable:value 'T' from line 1 field 3 not inserted, expecting type boolean failed to import table SQLException:importTable:value 'F' from line 1 field 3 not inserted, expecting type boolean failed to import table SQLException:importTable:value 't' from line 1 field 3 not inserted, expecting type boolean failed to import table SQLException:importTable:value 'f' from line 1 field 3 not inserted, expecting type boolean failed to import table +------+-------+-------+ | i | s | b | +======+=======+=======+ | 100 | 1 | true | | 101 | 0 | false | | 102 | 1 | true | | 103 | 0 | false | | 104 | 1 | true | | 105 | 0 | false | | 110 | 0 | false | | 111 | 1 | true | | 112 | 123 | true | | 113 | -123 | true | | 115 | TRUE | null | | 116 | FALSE | null | | 117 | true | true | | 118 | false | false | | 119 | TrUe | null | | 120 | FaLsE | null | | 121 | T | null | | 122 | F | null | | 123 | t | null | | 124 | f | null | | 200 | true | true | | 201 | false | false | | 210 | 0 | false | | 211 | 1 | true | | 217 | true | true | | 218 | false | false | +------+-------+-------+ 26 tuples (1.071ms) operation successful (80.934ms) ======== -- | Stefan.Manegold @ CWI.nl | DB Architectures (INS1) | | http://CWI.nl/~manegold/ | Science Park 123 (L321) | | Tel.: +31 (0)20 592-4212 | 1098 XG Amsterdam (NL) |
participants (4)
-
Martin Kersten -
Stefan de Konink -
Stefan Manegold -
Viktor Rosenfeld