Thread: casting from integer to boolean

Hi,

I'm running into a problem importing some MS Access tables into
PostgreSQL using mdbtools.  The schema is exported effectively with
mdb-schema, but several tables have boolean columns.  To deal with some
embedded double quotes in text fields, I'm exporting the tables as
INSERT commands using mdb-export.  The problem is that the boolean
fields are exported as integers (0, 1), i.e. the INSERT commands for the
booleans look like:

INSERT INTO my_table (var_bool) VALUES (0);
INSERT INTO my_table (var_bool) VALUES (1);

So this results in an error.  Unfortunately, there are many more columns
and it would be very difficult to write a regexp to replace the 0s and
1s with their quoted versions.  Is there any way to make such INSERT
commands be valid?  I have no experience with CAST, so any pointers
would be welcome.  Thanks.


Cheers,

--
Seb

On Wed, Mar 26, 2008 at 2:58 PM, Seb <spluque@gmail.com> wrote:
> INSERT INTO my_table (var_bool) VALUES (0);
> INSERT INTO my_table (var_bool) VALUES (1);
>
> So this results in an error.  Unfortunately, there are many more columns
> and it would be very difficult to write a regexp to replace the 0s and
> 1s with their quoted versions.  Is there any way to make such INSERT
> commands be valid?  I have no experience with CAST, so any pointers
> would be welcome.  Thanks.

INSERT INTO my_table (var_bool) VALUES ( CAST( 0 AS BOOLEAN ));
or
INSERT INTO my_table (var_bool) VALUES (0::BOOLEAN);


--
Regards,
Richard Broersma Jr.

On Wed, 26 Mar 2008 15:11:47 -0700,
"Richard Broersma" <richard.broersma@gmail.com> wrote:

[...]

> INSERT INTO my_table (var_bool) VALUES ( CAST( 0 AS BOOLEAN )); or
> INSERT INTO my_table (var_bool) VALUES (0::BOOLEAN);


Thanks Richard.  Is there a way to do it without changing the INSERT
command?  As I mentioned, there are many more columns of different
types, so finding and replacing the VALUES would be very difficult.


--
Seb

> Thanks Richard.  Is there a way to do it without changing the INSERT
> command?  As I mentioned, there are many more columns of different
> types, so finding and replacing the VALUES would be very difficult.

Can you import the data into a holding table (with columns defined
as integer) first, and then use a SQL statement to insert from there
into the final destination table (casting in the process) ?

On Wed, Mar 26, 2008 at 05:28:18PM -0500, Seb wrote:
> On Wed, 26 Mar 2008 15:11:47 -0700,
> "Richard Broersma" <richard.broersma@gmail.com> wrote:
>
> [...]
>
> > INSERT INTO my_table (var_bool) VALUES ( CAST( 0 AS BOOLEAN )); or
> > INSERT INTO my_table (var_bool) VALUES (0::BOOLEAN);
>
>
> Thanks Richard.  Is there a way to do it without changing the INSERT
> command?  As I mentioned, there are many more columns of different
> types, so finding and replacing the VALUES would be very difficult.

You could turn the problem around and make the bool columns into ints
(which should be a simple search-and-replace, I hope) and then write
something (again hopefully simple) to turn them all back into bools.
I.e. lots of:

  ALTER TABLE my_table ALTER var_bool TYPE bool USING var_bool::bool;


Either that or modify mdbtools.  I've just had a look at its source and
gave up trying to understand it rather quickly.


  Sam

--
Regards,
Richard Broersma Jr.

On Wed, 26 Mar 2008 17:34:59 -0500,
"Adam Rich" <adam.r@sbcglobal.net> wrote:

[...]

> Can you import the data into a holding table (with columns defined as
> integer) first, and then use a SQL statement to insert from there into
> the final destination table (casting in the process) ?

Yes, that would be possible, but then I would have to know which columns
need to be casted back into boolean.  I might find a way to do that
though.  Thanks.


--
Seb

"Adam Rich" <adam.r@sbcglobal.net> writes:
>> Thanks Richard.  Is there a way to do it without changing the INSERT
>> command?  As I mentioned, there are many more columns of different
>> types, so finding and replacing the VALUES would be very difficult.

> Can you import the data into a holding table (with columns defined
> as integer) first, and then use a SQL statement to insert from there
> into the final destination table (casting in the process) ?

If you're feeling desperate you could change the built-in integer
to boolean cast to be AS ASSIGNMENT rather than explicit-only.
Not sure this is a good idea --- one big problem with it is that
the change wouldn't be preserved by pg_dump, so if this is an
ongoing requirement rather than a one-time conversion effort that
would probably cause problems later.

            regards, tom lane

On Wed, 26 Mar 2008 22:46:08 +0000,
Sam Mason <sam@samason.me.uk> wrote:

[...]

> You could turn the problem around and make the bool columns into ints
> (which should be a simple search-and-replace, I hope) and then write
> something (again hopefully simple) to turn them all back into bools.
> I.e. lots of:

>   ALTER TABLE my_table ALTER var_bool TYPE bool USING var_bool::bool;


Yes! Good idea, I think I can collect the names of the tables and
columns with boolean fields and then use that as you say.  Thanks
everybody.


--
Seb

On Wed, Mar 26, 2008 at 05:28:18PM -0500, Seb wrote:
> > INSERT INTO my_table (var_bool) VALUES ( CAST( 0 AS BOOLEAN )); or
> > INSERT INTO my_table (var_bool) VALUES (0::BOOLEAN);
>
> Thanks Richard.  Is there a way to do it without changing the INSERT
> command?  As I mentioned, there are many more columns of different
> types, so finding and replacing the VALUES would be very difficult.

Don't think anyone mentioned it, but if you could get it to output
quotes around the value, like:

INSERT INTO my_table (var_bool) VALUES ('0');

It will also work.

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Please line up in a tree and maintain the heap invariant while
> boarding. Thank you for flying nlogn airlines.

On Thu, Mar 27, 2008 at 12:40 AM, Martijn van Oosterhout <kleptog@svana.org> wrote:

This is good to know.  There is an option in the ODBC driver to treat booleans as a char.  Maybe this would be a good solution.

--
Regards,
Richard Broersma Jr.

On Wed, 26 Mar 2008 18:03:06 -0500,
Seb <spluque@gmail.com> wrote:

> On Wed, 26 Mar 2008 22:46:08 +0000,
> Sam Mason <sam@samason.me.uk> wrote:

[...]

>> You could turn the problem around and make the bool columns into ints
>> (which should be a simple search-and-replace, I hope) and then write
>> something (again hopefully simple) to turn them all back into bools.
>> I.e. lots of:

>> ALTER TABLE my_table ALTER var_bool TYPE bool USING var_bool::bool;


> Yes! Good idea, I think I can collect the names of the tables and
> columns with boolean fields and then use that as you say.  Thanks
> everybody.

I found out that the above works only if int4 is used.  I had assumed
smallint should have worked, but got this error:

ERROR:  cannot cast type smallint to boolean

Someone posted (in an older thread) an alternative to deal with this:

---<---------------cut here---------------start-------------->---
ALTER TABLE my_table
  ALTER COLUMN var_bool TYPE boolean
    USING CASE WHEN var_bool = 0 THEN FALSE
      WHEN var_bool = 1 THEN TRUE
      ELSE NULL
      END;
---<---------------cut here---------------end---------------->---


--
Seb