[MonetDB-users] Bulk-importing boolean columns

Martin Kersten Martin.Kersten at cwi.nl
Wed Oct 19 14:31:02 CEST 2011

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 at lists.sourceforge.net
>> https://lists.sourceforge.net/lists/listinfo/monetdb-users

More information about the users-list mailing list