Обсуждение: How to convert a binary filed to an integer field?
I have an application in the product. Now, one status field needs to have three statuses instead of two. How to make a suchchange in PostgreSQL?
On 25/08/2010 20:29, wei725@lycos.com wrote: > I have an application in the product. Now, one status field needs to > have three statuses instead of two. How to make a such change in > PostgreSQL? Perhaps an enumerated type? http://www.postgresql.org/docs/8.4/static/datatype-enum.html Ray. -- Raymond O'Donnell :: Galway :: Ireland rod@iol.ie
On 08/25/2010 12:29 PM, wei725@lycos.com wrote: > I have an application in the product. Now, one status field needs to have three statuses instead of two. How to make asuch change in PostgreSQL? > I am going to assume that by binary you mean boolean. If so it already has three states NULL,True,False. -- Adrian Klaver adrian.klaver@gmail.com
> I am going to assume that by binary you mean boolean. If so it already has > three states NULL,True,False. Uh, not really, no. NULL indicates the absence or indeterminacy of a state, and has a number of properties that make it unsuitable for representing anything other than that. Use an enum. -- Regards, Peter Geoghegan
You are right. I typed the message in hurry before I left home this morning. It is the boolean type. Thanks for your suggestion.The NULL value may not work for jdbc. On the application level, a fixed set of constants is used to representthe three status, which are converted into an integer. ----- Original Message ----- From: "Adrian Klaver" <adrian.klaver@gmail.com> To: wei725@lycos.com Cc: pgsql-general@postgresql.org Sent: Wednesday, August 25, 2010 12:45:21 PM GMT -08:00 US/Canada Pacific Subject: Re: [GENERAL] How to convert a binary filed to an integer field? On 08/25/2010 12:29 PM, wei725@lycos.com wrote: > I have an application in the product. Now, one status field needs to have three statuses instead of two. How to make asuch change in PostgreSQL? > I am going to assume that by binary you mean boolean. If so it already has three states NULL,True,False. -- Adrian Klaver adrian.klaver@gmail.com
On Thu, Aug 26, 2010 at 12:28 AM, <wei725@lycos.com> wrote: > You are right. I typed the message in hurry before I left home this morning. It is the boolean type. Thanks for your suggestion.The NULL value may not work for jdbc. On the application level, a fixed set of constants is used to representthe three status, which are converted into an integer. TBH, you are probably best off taking those constants and writing them directly into the database (unless they happen to be dictionary pages), and applying a check constraint or using referential integrity to match against the known list. merlin
On Wed, Aug 25, 2010 at 9:29 PM, <wei725@lycos.com> wrote:
I have an application in the product. Now, one status field needs to have three statuses instead of two. How to make a such change in PostgreSQL?
ALTER TABLE product ALTER status TYPE int USING status::integer;
On Thu, Aug 26, 2010 at 1:47 PM, <wei725@lycos.com> wrote: > I know what I need to do in the application for the change. My problem is that I don't know a good process to make a suchchange in PostgreSQL. Since the application is in production already, do I need to create another field or convert thedata from the boolean field into the new field? recent versions of postgres allow you to do transformations w/alter table: alter table foo alter bool_col type text using case when bool_col then 'foo' else 'bar' end; expression can hook to a function if you want to do something really complex (yes this is awesome) :-) merlin
That shall work. I assume that 0 - false, 1 - true. Thank everyone for your suggestion. ----- Original Message ----- From: "Arjen Nienhuis" <a.g.nienhuis@gmail.com> To: wei725@lycos.com Cc: pgsql-general@postgresql.org Sent: Thursday, August 26, 2010 4:56:09 AM GMT -08:00 US/Canada Pacific Subject: Re: [GENERAL] How to convert a binary filed to an integer field? On Wed, Aug 25, 2010 at 9:29 PM, < wei725@lycos.com > wrote: I have an application in the product. Now, one status field needs to have three statuses instead of two. How to make a suchchange in PostgreSQL? ALTER TABLE product ALTER status TYPE int USING status::integer;
On Wed, Aug 25, 2010 at 1:29 PM, <wei725@lycos.com> wrote: > I have an application in the product. Now, one status field needs to have three statuses instead of two. How to make asuch change in PostgreSQL? You could also use bitstrings. -- To understand recursion, one must first understand recursion.
Unfortunately, the statement doesn't work. I get an error message about unable to convert boolean to integer. I have a look at the case statement for the conversion, but don't understand how it works. ----- Original Message ----- From: "Arjen Nienhuis" <a.g.nienhuis@gmail.com> To: wei725@lycos.com Cc: pgsql-general@postgresql.org Sent: Thursday, August 26, 2010 4:56:09 AM GMT -08:00 US/Canada Pacific Subject: Re: [GENERAL] How to convert a binary filed to an integer field? On Wed, Aug 25, 2010 at 9:29 PM, < wei725@lycos.com > wrote: I have an application in the product. Now, one status field needs to have three statuses instead of two. How to make a suchchange in PostgreSQL? ALTER TABLE product ALTER status TYPE int USING status::integer;
Maybe this works:
ALTER TABLE product ALTER status TYPE int USING CASE WHEN status THEN 1 ELSE 0 END;
If it doesn't, can you post the exact SQL you use and the exact error message?
On Mon, Aug 30, 2010 at 5:27 PM, <wei725@lycos.com> wrote:
Unfortunately, the statement doesn't work. I get an error message about unable to convert boolean to integer.
I have a look at the case statement for the conversion, but don't understand how it works.
----- Original Message -----
From: "Arjen Nienhuis" <a.g.nienhuis@gmail.com>
To: wei725@lycos.com
Cc: pgsql-general@postgresql.org
Sent: Thursday, August 26, 2010 4:56:09 AM GMT -08:00 US/Canada Pacific
Subject: Re: [GENERAL] How to convert a binary filed to an integer field?On Wed, Aug 25, 2010 at 9:29 PM, < wei725@lycos.com > wrote:
I have an application in the product. Now, one status field needs to have three statuses instead of two. How to make a such change in PostgreSQL?
ALTER TABLE product ALTER status TYPE int USING status::integer;