Hi,
I basically want to change a boolean column to char. The boolean
column has a default of true. The char column should have 'f' for
false and 't' for true. I think that an SQL statement like the
following should work, but it doesn't:
blog=> select version();
version
------------------------------------------------------------------------------------------------------------
PostgreSQL 8.0.7 on i486-pc-linux-gnu, compiled by GCC
gcc-4.0.gcc-opt (GCC) 4.0.3 (Ubuntu 4.0.3-1ubuntu1)
(1 запись)
blog=> \d posts
Таблица "public.posts"
Колонка | Тип | Модификаторы
----------+-----------------------------+-------------------------------------------------------
id | integer | not null default
nextval('public.posts_id_seq'::text)
title | text |
body | text |
created | timestamp without time zone |
modified | timestamp without time zone |
deleted | boolean | default false
Индексы:
"posts_pkey" PRIMARY KEY, btree (id)
blog=> alter table posts alter column deleted type char(1) using (case
when deleted then 't' else 'f' end), alter column deleted set default
'f';
ERROR: default for column "deleted" cannot be cast to type "pg_catalog.bpchar"
blog=> alter table posts alter column deleted set default 'f', alter
column deleted type char(1) using (case when deleted then 't' else 'f'
end);
ERROR: default for column "deleted" cannot be cast to type "pg_catalog.bpchar"
How is this supposed to work? Note that I don't need a solution for
this special case, but rather want to know, how it is supposed to work
- am I doing it wrong or is it not possible to do it like that (and is
that because the SQL standard or because of how postgresql implements
it?)
Thanks
Markus Bertheau
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
