From: Markus Bertheau <mbertheau.pg@googlemail.com>
To: pgsql-sql@postgresql.org
Subject: alter column type from boolean to char with default doesn't work
Date: Wed, 2 Aug 2006 19:46:36 +0700
Message-ID: <684362e10608020546t16e6d3e8nf6756abd15a21a68@mail.gmail.com> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: mbertheau.pg@googlemail.com
Subject: Re: alter column type from boolean to char with default doesn't work
In-Reply-To: <684362e10608020546t16e6d3e8nf6756abd15a21a68@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox