Thread: Use true and false when bulk-exporting boolean data

Hi,

I need to move data from PostgreSQL to MonetDB and also bulk-import data
into MonetDB that was bulk-exported from PostgreSQL by other people. My
problem is that boolean data is exported by PostgreSQL using the values
"t" and "f" (unquoted) and that MonetDB expects "true" and "false".  Is
there a way to change how boolean columns are exported?

I'm using the following command to export:

  \copy table to 'filename' WITH NULL 'NULL';

And to import (in MonetDB):

  copy into table from '/path/to/filename' USING DELIMITERS '\t','\n','';

Cheers,
Viktor

I was looking for an easier, more automatic way, but I wrote a few
scripts that wrapped the boolean fields in case statements as suggested.

Thanks,
Viktor

Henry Drexler wrote:

> couldn't you just wrap it in a case statement to change the t to true
> etc...?
>
> On Mon, Oct 17, 2011 at 2:29 PM, Viktor Rosenfeld <listuser36@googlemail.com
> > wrote:
>
> > Hi,
> >
> > I need to move data from PostgreSQL to MonetDB and also bulk-import data
> > into MonetDB that was bulk-exported from PostgreSQL by other people. My
> > problem is that boolean data is exported by PostgreSQL using the values
> > "t" and "f" (unquoted) and that MonetDB expects "true" and "false".  Is
> > there a way to change how boolean columns are exported?
> >
> > I'm using the following command to export:
> >
> >  \copy table to 'filename' WITH NULL 'NULL';
> >
> > And to import (in MonetDB):
> >
> >  copy into table from '/path/to/filename' USING DELIMITERS '\t','\n','';
> >
> > Cheers,
> > Viktor
> >
> > --
> > Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> > To make changes to your subscription:
> > http://www.postgresql.org/mailpref/pgsql-general
> >

On 18 Oct 2011, at 17:54, Viktor Rosenfeld wrote:

> I was looking for an easier, more automatic way, but I wrote a few
> scripts that wrapped the boolean fields in case statements as suggested.


You are aware that COPY accepts a query as well?
You could also have created a VIEW over that table that translates those values.

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.

Hi Alban,

in the end I used a COPY statement with a query and a CASE statement as
suggested by Henry.

Cheers,
Viktor

Alban Hertroys wrote:

> On 18 Oct 2011, at 17:54, Viktor Rosenfeld wrote:
>
> > I was looking for an easier, more automatic way, but I wrote a few
> > scripts that wrapped the boolean fields in case statements as suggested.
>
>
> You are aware that COPY accepts a query as well?
> You could also have created a VIEW over that table that translates those values.
>
> Alban Hertroys
>
> --
> If you can't see the forest for the trees,
> cut the trees and you'll see there is no forest.
>