[MonetDB-users] Bulk-importing boolean columns

Stefan Manegold Stefan.Manegold at cwi.nl
Wed Oct 19 15:04:43 CEST 2011


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) |




More information about the users-list mailing list