alter column type from boolean to char with default doesn't work

From: "Markus Bertheau" <mbertheau(dot)pg(at)googlemail(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: alter column type from boolean to char with default doesn't work
Date: 2006-08-02 12:46:36
Message-ID: 684362e10608020546t16e6d3e8nf6756abd15a21a68@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Penchalaiah P. 2006-08-02 12:54:11 Problem while using lo_import and lo_export
Previous Message John Tregea 2006-08-02 03:55:46 Re: Joining a result set from four (4) tables