Обсуждение: Inserting boolean types as an alias?
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.
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
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
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.
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
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.