Thread: In-place conversion of type bool

Hi,

I am converting a database to PostgreSQL. I use ODBC to create the
PostgreSQL database and copy the data.

The database says that it's bool implementation is char(1), just as
PostgreSQL does. I can copy te data OK, but I would like to change the
actual type of the column from char(1) to bool. Is that possible without
copying the column to a temporary column, dropping the old column and
renaming the temporary columns to the old column?

I also have another table that has mac addresses stored *exactly* in the
same format as PostgreSQL macaddr type that are used as primary key and
dropping that key would mean a major headache. In this case I would like
to be able to change the type from varchar to macaddr.

TIA


--
Groeten,

Joost Kraaijeveld
Askesis B.V.
Molukkenstraat 14
6524NB Nijmegen
tel: 024-3888063 / 06-51855277
fax: 024-3608416
web: www.askesis.nl

"Joost Kraaijeveld" <J.Kraaijeveld@Askesis.nl> writes:
> The database says that it's bool implementation is char(1), just as
> PostgreSQL does. I can copy te data OK, but I would like to change the
> actual type of the column from char(1) to bool. Is that possible without
> copying the column to a temporary column, dropping the old column and
> renaming the temporary columns to the old column?

ALTER TABLE ... ALTER COLUMN TYPE might help you.  Use the USING clause
if you need a non-default data conversion -- in this case it might look
like USING (col = '1') or some such.

(This is probably not physically more efficient than making a temp
table, however.)

            regards, tom lane

Hi Tom,

On Wed, 2008-08-13 at 21:27 -0400, Tom Lane wrote:
> "Joost Kraaijeveld" <J.Kraaijeveld@Askesis.nl> writes:
> > The database says that it's bool implementation is char(1), just as
> > PostgreSQL does. I can copy te data OK, but I would like to change the
> > actual type of the column from char(1) to bool. Is that possible without
> > copying the column to a temporary column, dropping the old column and
> > renaming the temporary columns to the old column?
>
> ALTER TABLE ... ALTER COLUMN TYPE might help you.  Use the USING clause
> if you need a non-default data conversion -- in this case it might look
> like USING (col = '1') or some such.


ALTER TABLE odbcdest ALTER COLUMN odbc_bool TYPE bool

gives:

ERROR:  column "odbc_bool" cannot be cast to type "bool"

********** Error **********

ERROR: column "odbc_bool" cannot be cast to type "bool"
SQL state: 42804


>
> (This is probably not physically more efficient than making a temp
> table, however.)

Could you elaborate on this? I have tried something like this, but when
the column in question is a column that is used in foreign keys I am
forced to drop any foreign keys that point to this column. Which is quit
annoying on a 200 table database with lots of foreign keys.

TIA

--
Groeten,

Joost Kraaijeveld
Askesis B.V.
Molukkenstraat 14
6524NB Nijmegen
tel: 024-3888063 / 06-51855277
fax: 024-3608416
web: www.askesis.nl

Joost Kraaijeveld wrote:

> > ALTER TABLE ... ALTER COLUMN TYPE might help you.  Use the USING clause
> > if you need a non-default data conversion -- in this case it might look
> > like USING (col = '1') or some such.
>
> ALTER TABLE odbcdest ALTER COLUMN odbc_bool TYPE bool
>
> gives:
>
> ERROR:  column "odbc_bool" cannot be cast to type "bool"

You need a USING clause, as Tom suggests above (even giving an example).

--
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.