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
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
Show quoted text
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 Mon, Oct 17, 2011 at 3:11 PM, Henry Drexler <alonup8tb@gmail.com> wrote:
couldn't you just wrap it in a case statement to change the t to true
etc...?
example:
select
case when (1=1) = true then 'true' else 'false' end
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:
Show quoted text
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:
Show quoted text
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.