Thread: alter column type from boolean to char with default doesn't work
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.7on 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
"Markus Bertheau" <mbertheau.pg@googlemail.com> writes:
> 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:
Hmm ... the way I would have expected to work is
alter table posts alter column deleted drop default, alter column deleted type char(1) using (case when deleted then
't'else 'f' end), alter column deleted set default 'f';
but that does not work either --- you have to do it in more than one
command:
begin;
alter table posts alter column deleted drop default;
alter table posts alter column deleted type char(1) using (case when deleted then 't' else 'f' end), alter column
deletedset default 'f';
commit;
We could fix this by tweaking ATPrepCmd to schedule drop-default
subcommands in an earlier pass than alter-type, and set-default
subcommands afterwards. However I think the way it's done now
was chosen to avoid surprising behavior in corner cases like
alter table foo alter column bar set default ..., alter column bar drop default;
You'd expect this to leave you with no default, but with the change
the DROP part would be re-ordered to occur first. So maybe the
cure is worse than the disease. OTOH that's a pretty silly example,
whereas wanting to ALTER TYPE and fix the default in a single command
is quite reasonable. Thoughts?
regards, tom lane
On Wed, 2006-08-02 at 09:19 -0400, Tom Lane wrote:
> "Markus Bertheau" <mbertheau.pg@googlemail.com> writes:
> > 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:
>
> Hmm ... the way I would have expected to work is
>
> alter table posts
> alter column deleted drop default,
> alter column deleted type char(1)
> using (case when deleted then 't' else 'f' end),
> alter column deleted set default 'f';
Perhaps it is easiest to allow the user to specify the new default after
USING?
USING already indicates that a direct conversion may not be what the
user wants to do with it? alter table posts alter column deleted type char(1) using (case when
deletedthen 't' else 'f' end) default 'f';
Actually, perhaps it would be best to wrap the old default with the
USING expression, evaluating when the default was a scalar and keeping
the entire equation otherwise?
The below SQL would give a new default of 'f' (evaluated scalar):
create table posts (deleted bool default false); alter table posts alter column deleted type char(1)
using (case when deleted then 't' else 'f' end);
This would result in the default (case when somefunc() then 't' else 'f'
end): create table posts (deleted bool default somefunc()); alter table posts alter column deleted
typechar(1) using (case when deleted then 't' else 'f' end);
> but that does not work either --- you have to do it in more than one
> command:
>
> begin;
> alter table posts
> alter column deleted drop default;
> alter table posts
> alter column deleted type char(1)
> using (case when deleted then 't' else 'f' end),
> alter column deleted set default 'f';
> commit;
>
> We could fix this by tweaking ATPrepCmd to schedule drop-default
> subcommands in an earlier pass than alter-type, and set-default
> subcommands afterwards. However I think the way it's done now
> was chosen to avoid surprising behavior in corner cases like
>
> alter table foo
> alter column bar set default ...,
> alter column bar drop default;
>
> You'd expect this to leave you with no default, but with the change
> the DROP part would be re-ordered to occur first. So maybe the
> cure is worse than the disease. OTOH that's a pretty silly example,
> whereas wanting to ALTER TYPE and fix the default in a single command
> is quite reasonable. Thoughts?
>
> regards, tom lane
>
> ---------------------------(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
>
--
Rod Taylor <pg@rbt.ca> writes:
> On Wed, 2006-08-02 at 09:19 -0400, Tom Lane wrote:
>> Hmm ... the way I would have expected to work is
>>
>> alter table posts
>> alter column deleted drop default,
>> alter column deleted type char(1)
>> using (case when deleted then 't' else 'f' end),
>> alter column deleted set default 'f';
> Perhaps it is easiest to allow the user to specify the new default after
> USING?
He already did --- I don't want to add some random new syntax for this.
Maybe we could hack things so that if both an ALTER TYPE and a SET
DEFAULT operation are present, we implicitly add a DROP DEFAULT at the
start. But leave the timing of any explicitly specified DROP DEFAULT
as-is.
regards, tom lane
Tom Lane wrote:
> Rod Taylor <pg@rbt.ca> writes:
> > On Wed, 2006-08-02 at 09:19 -0400, Tom Lane wrote:
> >> Hmm ... the way I would have expected to work is
> >>
> >> alter table posts
> >> alter column deleted drop default,
> >> alter column deleted type char(1)
> >> using (case when deleted then 't' else 'f' end),
> >> alter column deleted set default 'f';
>
> > Perhaps it is easiest to allow the user to specify the new default after
> > USING?
>
> He already did --- I don't want to add some random new syntax for this.
>
> Maybe we could hack things so that if both an ALTER TYPE and a SET
> DEFAULT operation are present, we implicitly add a DROP DEFAULT at the
> start. But leave the timing of any explicitly specified DROP DEFAULT
> as-is.
That seems reasonable. I assume it'd throw a notice in that case.
Alternatively, you already have the USING clause to tell you how to
alter the data. How about using it to alter the default as well?
Replace instances of column references with the old default. In this
case, the default would go from
DEFAULT ('f'::boolean)
to
DEFAULT (case when ('f'::boolean) then 't' else 'f' end)
No syntax or grammar change involved, but I'm not sure the additional
semantics adhere to the rule of minimum surprise.
If you wanted to support the DROP CONSTRAINT, ADD CONSTRAINT (which
seems useful) while without confusing it with ADD, DROP (I can't
imagine a use for this), then perhaps tweaking the grammar would be the
answer so that either DROP CONSTRAINT must be the first or ADD
CONSTRAINT must be the last part of an ALTER.
Drew
"Andrew Hammond" <andrew.george.hammond@gmail.com> writes:
> Alternatively, you already have the USING clause to tell you how to
> alter the data. How about using it to alter the default as well?
The reasons not to do that are already set forth in the ALTER TABLE
man page.
regards, tom lane