Thread: 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.