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
On Wed, Mar 26, 2008 at 3:28 PM, Seb <spluque@gmail.com> 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.
My understanding is that a temporary staging table could be used to receive
all of your data into postgresql. Next, you can perform most of your data
scrubbing within postgres with simple update statements.
Last, you and insert to the cleaned data with an insert+select statement
with the appropriate casts. this way you only need to do the casting in a
single statement.
--
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/
Show quoted text
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:
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.
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