Thread: Inserting boolean types as an alias?

Inserting boolean types as an alias?

From
Nick
Date:
Hello I am new to this site and also a student. I am working on an assignment
and was wondering if there is a way to make an alias for a boolean? For
example when I am making a table called club_games and it must contain the
memberID, gameID, count, and status as column fields. When I enter the data
for status I want it to say either "unavailable" for false and "available"
for true. but I don't know how to do that.
This is what I have currently for the line that I am specifically talking
about:

INSERT INTO club_Games(memberID, gameID, hardwareID, count, status)
     VALUES    ((SELECT memberID FROM members WHERE name = 'Fred Flinstone'),
(SELECT gameID FROM games WHERE name = 'Jurrasic Park'), NULL, 1, 'true');

when I do the SELECT * FROM club_Games this is my output:

id     gameid        memberid      hardwareid            count
status
1   12345zzzzz       A12345           <null>               1
t

Where it says t for status I wanted it to say available but mean true for
boolean, but I don't know how to do that. Thanks to all for help.

 My file is attached if you would like to view my assignment.  task5.txt
<http://postgresql.1045698.n5.nabble.com/file/n5781328/task5.txt>



--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Inserting-boolean-types-as-an-alias-tp5781328.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


Re: Inserting boolean types as an alias?

From
Adrian Klaver
Date:
On 12/02/2013 03:46 PM, Nick wrote:
> Hello I am new to this site and also a student. I am working on an assignment
> and was wondering if there is a way to make an alias for a boolean? For
> example when I am making a table called club_games and it must contain the
> memberID, gameID, count, and status as column fields. When I enter the data
> for status I want it to say either "unavailable" for false and "available"
> for true. but I don't know how to do that.
> This is what I have currently for the line that I am specifically talking
> about:
>
> INSERT INTO club_Games(memberID, gameID, hardwareID, count, status)
>      VALUES    ((SELECT memberID FROM members WHERE name = 'Fred Flinstone'),
> (SELECT gameID FROM games WHERE name = 'Jurrasic Park'), NULL, 1, 'true');
>
> when I do the SELECT * FROM club_Games this is my output:
>
> id     gameid        memberid      hardwareid            count
> status
> 1   12345zzzzz       A12345           <null>               1
> t
>
> Where it says t for status I wanted it to say available but mean true for
> boolean, but I don't know how to do that. Thanks to all for help.
>

If the field has to be a boolean, then alias it on the select:

select case when status = 't' then 'available' else 'unavailable' end
from club_Games

otherwise make status a text field and just enter 'available' or
'unavailable'

--
Adrian Klaver
adrian.klaver@gmail.com


Re: Inserting boolean types as an alias?

From
John R Pierce
Date:
On 12/2/2013 3:46 PM, Nick wrote:
> Hello I am new to this site and also a student. I am working on an assignment
> and was wondering if there is a way to make an alias for a boolean? For
> example when I am making a table called club_games and it must contain the
> memberID, gameID, count, and status as column fields. When I enter the data
> for status I want it to say either "unavailable" for false and "available"
> for true. but I don't know how to do that.

you could use an enum, or you could use a case statement on your query,
like,

SELECT memberID, gameID, hardwareID, count, case when status then 'available' else 'unavailable' end FROM club_Games




--
john r pierce                                      37N 122W
somewhere on the middle of the left coast



Re: Inserting boolean types as an alias?

From
David Johnston
Date:
John R Pierce wrote
> On 12/2/2013 3:46 PM, Nick wrote:
>> Hello I am new to this site and also a student. I am working on an
>> assignment
>> and was wondering if there is a way to make an alias for a boolean? For
>> example when I am making a table called club_games and it must contain
>> the
>> memberID, gameID, count, and status as column fields. When I enter the
>> data
>> for status I want it to say either "unavailable" for false and
>> "available"
>> for true. but I don't know how to do that.
>
> you could use an enum, or you could use a case statement on your query,
> like,
>
> SELECT memberID, gameID, hardwareID, count, case when status then
> 'available' else 'unavailable' end FROM club_Games

Not sure an enum would work since the source table has the "status" column
defined as boolean...

Condition conversion requires the use of the "CASE" expression.  One point
not made is that the column allows for NULL.  I like to avoid 3-value logic
thinking when possible so I'd suggest:

CASE WHEN status IS NULL THEN 'Unknown'
         WHEN status = true THEN 'Available'
         ELSE 'Unavailable'
END::varchar AS status_description

Then, if you need the logic elsewhere put that into a SQL function so you
can change the words without having to change every location the conversion
may be used.

You may also want a "description_to_status" function to perform the inverse.

David J.





--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Inserting-boolean-types-as-an-alias-tp5781328p5781337.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


Re: Inserting boolean types as an alias?

From
John R Pierce
Date:
On 12/2/2013 5:18 PM, David Johnston wrote:
> Not sure an enum would work since the source table has the "status" column
> defined as boolean...

well, you'd need to alter the table to use an enum, of course.



--
john r pierce                                      37N 122W
somewhere on the middle of the left coast



Re: Inserting boolean types as an alias?

From
Michal Politowski
Date:
On Mon,  2 Dec 2013 15:46:17 -0800, Nick wrote:
[...]
> This is what I have currently for the line that I am specifically talking
> about:
>
> INSERT INTO club_Games(memberID, gameID, hardwareID, count, status)
>      VALUES    ((SELECT memberID FROM members WHERE name = 'Fred Flinstone'),
> (SELECT gameID FROM games WHERE name = 'Jurrasic Park'), NULL, 1, 'true');
>
> when I do the SELECT * FROM club_Games this is my output:
>
> id     gameid        memberid      hardwareid            count
> status
> 1   12345zzzzz       A12345           <null>               1
> t
>
> Where it says t for status I wanted it to say available but mean true for
> boolean, but I don't know how to do that. Thanks to all for help.

Alternate suggestion: If you want to use a boolean column here,
maybe you could consider naming the column "available", not "status".

id     gameid        memberid      hardwareid    count     available
1      12345zzzzz    A12345        <null>        1         t

--
Michał Politowski
Talking has been known to lead to communication if practiced carelessly.