[MonetDB-users] Bulk-importing boolean columns

Stefan Manegold Stefan.Manegold at cwi.nl
Wed Oct 19 10:21:47 CEST 2011


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




More information about the users-list mailing list