How to convert a binary filed to an integer field?

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 a such 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 represent the 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 a such 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 represent the 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 such change in PostgreSQL. Since the application is in production already, do I need to create another field or convert the data 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 such change 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 a such 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 such change 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: