Alter column...using failure under 9.0.4
Dear list,
Ive a strange issue here. Ive a table that Im sure the column type is
boolean. I can see the datatype is boolean on PgAdmin.
Nevertheless, when I issue this command
----------------------------------------------------------------------------
----
alter table usuario alter column ativo type smallint using (case when ativo
then 1 else 0 end);
----------------------------------------------------------------------------
----
I get the following error:
----------------------------------------------------------------------------
----
ERROR: argument of IS FALSE must be type boolean, not type smallint
********** Error **********
ERROR: argument of IS FALSE must be type boolean, not type smallint
SQL state: 42804
----------------------------------------------------------------------------
----
I already run Vacuum just to be sure, but still not working.
I have a script with hundred similar lines (for other tables) without any
error, just this table.
Any hints?
Edson Carlos Ericksson Richter
SimKorp Infomática Ltda
Fone:
(51) 3366-7964
Celular:
(51) 8585-0796
Embedded Image
<http://www.simkorp.com.br/> www.simkorp.com.br
Attachments:
Hello,
you could check for indices or something like that.
Björn
Am 25.09.2011 um 17:17 schrieb Edson Carlos Ericksson Richter:
Show quoted text
Dear list,
I’ve a strange issue here. I’ve a table that I’m sure the column type is boolean. I can see the datatype is boolean on PgAdmin.
Nevertheless, when I issue this command
--------------------------------------------------------------------------------
alter table usuario alter column ativo type smallint using (case when ativo then 1 else 0 end);
--------------------------------------------------------------------------------I get the following error:
--------------------------------------------------------------------------------
ERROR: argument of IS FALSE must be type boolean, not type smallint********** Error **********
ERROR: argument of IS FALSE must be type boolean, not type smallint
SQL state: 42804
--------------------------------------------------------------------------------I already run Vacuum just to be sure, but still not working.
I have a script with hundred similar lines (for other tables) without any error, just this table.
Any hints?
Edson Carlos Ericksson Richter
SimKorp Infomática Ltda
Fone:
(51) 3366-7964
Celular:
(51) 8585-0796
<image001.jpg>
www.simkorp.com.br
=?iso-8859-1?Q?Bj=F6rn_H=E4user?= <bjoernhaeuser@gmail.com> writes:
Am 25.09.2011 um 17:17 schrieb Edson Carlos Ericksson Richter:
alter table usuario alter column ativo type smallint using (case when ativo then 1 else 0 end);
ERROR: argument of IS FALSE must be type boolean, not type smallint
you could check for indices or something like that.
Yeah, looks like expression index or CHECK constraint or something
similar that includes "ativo IS FALSE".
Note to hackers: I wonder whether we could make this a bit more
user-friendly by providing a CONTEXT line that shows which table
property we're trying to convert.
regards, tom lane
That's it: a check constraint I was not aware of...
Thanks!
Edson
-----Mensagem original-----
De: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] Em nome de Tom Lane
Enviada em: domingo, 25 de setembro de 2011 13:04
Para: pgsql-general@postgresql.org; pgsql-hackers@postgresql.org
Assunto: Re: [GENERAL] Alter column...using failure under 9.0.4
=?iso-8859-1?Q?Bj=F6rn_H=E4user?= <bjoernhaeuser@gmail.com> writes:
Am 25.09.2011 um 17:17 schrieb Edson Carlos Ericksson Richter:
alter table usuario alter column ativo type smallint using (case when
ativo then 1 else 0 end);
ERROR: argument of IS FALSE must be type boolean, not type smallint
you could check for indices or something like that.
Yeah, looks like expression index or CHECK constraint or something similar
that includes "ativo IS FALSE".
Note to hackers: I wonder whether we could make this a bit more
user-friendly by providing a CONTEXT line that shows which table property
we're trying to convert.
regards, tom lane
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make
changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general