Обсуждение: Convert boolean field to a 0 or a 1 in a select statement

On 01/09/2015 08:47 AM, JORGE MALDONADO wrote:
> I have a SELECT statement which contains a boolean field. The result
> of this query must display '0' for false or '1' for true. I will be
> generating a text file with this information and a zero or a one is
> required. I have tried using the CASE statement without success.
>
> CAST(fce_subdivision as CHAR) AS fce_subdivision_aux,
> CASE CAST(fce_subdivision as CHAR)
>     WHEN 'f' THEN '0'
>     WHEN 't' THEN '1'
> END
>
>

I recommend reading the docs to ensure this casting is explicitly
documented so it won't stop working in future releases but this works
for me:
select true::int:char(1);

Where you would replace "true" with the name of your boolean field.

Cheers,
Steve

JORGE MALDONADO wrote
> I have a SELECT statement which contains a boolean field. The result of
> this query must display '0' for false or '1' for true. I will be
> generating
> a text file with this information and a zero or a one is required. I have
> tried using the CASE statement without success.
>
> CAST(fce_subdivision as CHAR) AS fce_subdivision_aux,
> CASE CAST(fce_subdivision as CHAR)
>     WHEN 'f' THEN '0'
>     WHEN 't' THEN '1'
> END
>
> I will very much appreciate your feedback.
>
> Respectfully,
> Jorge Maldonado

One of us is missing something because this is the direct application of
your requirement as stated:

CASE WHEN fce_subdivision THEN '1' ELSE '0' END

David J.




--
View this message in context:
http://postgresql.nabble.com/Convert-boolean-field-to-a-0-or-a-1-in-a-select-statement-tp5833411p5833422.html
Sent from the PostgreSQL - novice mailing list archive at Nabble.com.

On 13 Jan 2015 22:18, "Zelaznik, Steve" <szelazni@aegonusa.com> wrote:
>
> I’m more explicit with my CASE statements, explicitly putting the equals sign in place.  Such as in the attachment “explicit_equals.gif”.  I’m not sure how your CASE statement works, but generally you can only omit the equals sign when you’re dealing with a pure Boolean value, such as the other attachment “pure_ bool.gif”.
>

I guess

Select case col1 when 1 then true when 0 then false end as int_as_bool from table1;

Should work as well (I need to try it out to be sure).

In you case anyways the columns is "pure boolean". So I guess you can skip converting to char in case statement.

>  
>
> -Steve Z
>
>  
>
> From: pgsql-novice-owner@postgresql.org [mailto:pgsql-novice-owner@postgresql.org] On Behalf Of Sameer Kumar
> Sent: Monday, January 12, 2015 1:31 AM
> To: JORGE MALDONADO
> Cc: pgsql-novice@postgresql.org
> Subject: Re: [NOVICE] Convert boolean field to a 0 or a 1 in a select statement
>
>  
>
>  
>
> On Sat, Jan 10, 2015 at 12:47 AM, JORGE MALDONADO <jorgemal1960@gmail.com> wrote:
>
> CAST(fce_subdivision as CHAR) AS fce_subdivision_aux,
>
> CASE CAST(fce_subdivision as CHAR)
>
>     WHEN 'f' THEN '0'
>
>     WHEN 't' THEN '1'
>
> END
>

>  
>
> ​Why do you want to cast it first to char?
>
>  
>
> And anyways what is the issue with casting it to number first and then casting it to char? 
>
>  
>
>  
>
>
>
> Best Regards,
>
> Sameer Kumar | Database Consultant
>
> ASHNIK PTE. LTD.
>
> 101 Cecil Street, #11-11 Tong Eng Building, Singapore 069533
>
> M: +65 8110 0350  T: +65 6438 3504 | www.ashnik.com
>
>  
>
>  
>
> This email may contain confidential, privileged or copyright material and is solely for the use of the intended recipient(s).