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.