Re: [HACKERS] ALTER TABLE ( smallinto - boolean ) ...
On Mon, 29 Aug 2005, Tom Lane wrote: Marc G. Fournier [EMAIL PROTECTED] writes: # ALTER TABLE table ALTER COLUMN field1 type boolean; ERROR: column field1 cannot be cast to type pg_catalog.bool Should this not work? No, because there's no built-in cast from smallint to bool. You could do something like ... type boolean using case when field1=0 then false else true end; 'k, I just took a read through the CREATE CAST man page, and don't think I can use that for this, but is there some way I can create a cast for this, so that we don't have to go through the complete application and change VALUES ( 0 ); to VALUES ( '0' ); ... Again, from reading the man page, I'm guessing not, but just want to make sure that I haven't missed anything ... Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] ALTER TABLE ( smallinto - boolean ) ...
Marc G. Fournier [EMAIL PROTECTED] writes: On Mon, 29 Aug 2005, Tom Lane wrote: No, because there's no built-in cast from smallint to bool. 'k, I just took a read through the CREATE CAST man page, and don't think I can use that for this, Sure you can. Make a SQL or PLPGSQL function that does the conversion you want and then create a cast using it. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] ALTER TABLE ( smallinto - boolean ) ...
Tom Lane wrote: Marc G. Fournier [EMAIL PROTECTED] writes: On Mon, 29 Aug 2005, Tom Lane wrote: No, because there's no built-in cast from smallint to bool. 'k, I just took a read through the CREATE CAST man page, and don't think I can use that for this, Sure you can. Make a SQL or PLPGSQL function that does the conversion you want and then create a cast using it. That probably won't help him much with values(0): andrew=# create function ibool(smallint) returns boolean language sql as $$ select $1 0 $$; CREATE FUNCTION andrew=# create cast (smallint as boolean) with function ibool(smallint) as implicit; CREATE CAST andrew=# insert into foobool values(0); ERROR: column x is of type boolean but expression is of type integer HINT: You will need to rewrite or cast the expression. Is there a way to make the builtin int to bool cast implicit? cheers andrew ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] ALTER TABLE ( smallinto - boolean ) ...
Andrew Dunstan [EMAIL PROTECTED] writes: Tom Lane wrote: Sure you can. Make a SQL or PLPGSQL function that does the conversion you want and then create a cast using it. That probably won't help him much with values(0): If I remember the context of the original request, it wasn't about that anyway, but about dealing with an existing smallint column. Is there a way to make the builtin int to bool cast implicit? I think you'd have to go and hack the pg_cast entry ... but that cast is new in 8.1 anyway, so it doesn't apply to Marc's problem (yet). If we want to make it cover that specific scenario, changing it to AS ASSIGNMENT would be sufficient; I don't think it needs to be IMPLICIT. (I generally find cross-type-category implicit casts to be dangerous.) regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] ALTER TABLE ( smallinto - boolean ) ...
On Thu, 1 Sep 2005, Tom Lane wrote: Andrew Dunstan [EMAIL PROTECTED] writes: Tom Lane wrote: Sure you can. Make a SQL or PLPGSQL function that does the conversion you want and then create a cast using it. That probably won't help him much with values(0): If I remember the context of the original request, it wasn't about that anyway, but about dealing with an existing smallint column. Nope, actually, the original was to just convert an existing table from using smallint-boolean, but what I'm looking at with the CREATE CAST is to avoid reducing the # of changes that I have to make to the existing application, so being able to auto-cast 0-'f' on an INSERT/UPDATE would help wtih that ... The app still needs to be fixed, but this would allow for the initial change to be made a bit easier ... Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] ALTER TABLE ( smallinto - boolean ) ...
Tom Lane [EMAIL PROTECTED] writes: You could do something like ... type boolean using case when field1=0 then false else true end; Or you could save typing and just use USING field10 Odd that everyone did a CASE for that. -- greg ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] ALTER TABLE ( smallinto - boolean ) ...
On Mon, Aug 29, 2005 at 08:15:41PM -0300, Marc G. Fournier wrote: I have a table with several 'smallint' fields that I'd like to convert to booleean ... the data in each is either 0 or 1, and: # select '1'::boolean; bool -- t (1 row) # select '0'::boolean; bool -- f (1 row) so they do cast as expected ... but, if I try to do the ALTER, I get: # ALTER TABLE table ALTER COLUMN field1 type boolean; ERROR: column field1 cannot be cast to type pg_catalog.bool Should this not work? If not, is there a way to do it so that it will, without having to reload the whole table? ALTER TABLE table ALTER COLUMN field1 TYPE boolean USING CASE field1 WHEN 0 THEN false ELSE true END; /* or something to this effect */ HTH :) Cheers, D -- David Fetter [EMAIL PROTECTED] http://fetter.org/ phone: +1 510 893 6100 mobile: +1 415 235 3778 Remember to vote! ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] ALTER TABLE ( smallinto - boolean ) ...
On Mon, 2005-08-29 at 20:15 -0300, Marc G. Fournier wrote: I have a table with several 'smallint' fields that I'd like to convert to booleean ... the data in each is either 0 or 1, and: # ALTER TABLE table ALTER COLUMN field1 type boolean; ERROR: column field1 cannot be cast to type pg_catalog.bool Should this not work? If not, is there a way to do it so that it will, without having to reload the whole table? development=# select '0'::smallint::boolean; ERROR: cannot cast type smallint to boolean You were casting an unknown to boolean. Anyway, USING is what you're looking for: ALTER TABLE table ALTER COLUMN field1 TYPE boolean USING CASE WHEN field1 = 0 THEN FALSE WHEN field1 = 1 THEN TRUE ELSE NULL END; -- ---(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
Re: [HACKERS] ALTER TABLE ( smallinto - boolean ) ...
Marc G. Fournier [EMAIL PROTECTED] writes: # ALTER TABLE table ALTER COLUMN field1 type boolean; ERROR: column field1 cannot be cast to type pg_catalog.bool Should this not work? No, because there's no built-in cast from smallint to bool. You could do something like ... type boolean using case when field1=0 then false else true end; regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
