Lists: | pgsql-general |
---|
From: | Nick <ngalewski(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Inserting boolean types as an alias? |
Date: | 2013-12-02 23:46:17 |
Message-ID: | 1386027977319-5781328.post@n5.nabble.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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.
From: | Adrian Klaver <adrian(dot)klaver(at)gmail(dot)com> |
---|---|
To: | Nick <ngalewski(at)gmail(dot)com>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: Inserting boolean types as an alias? |
Date: | 2013-12-02 23:55:49 |
Message-ID: | 529D1E05.4020108@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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(dot)klaver(at)gmail(dot)com
From: | John R Pierce <pierce(at)hogranch(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Inserting boolean types as an alias? |
Date: | 2013-12-02 23:59:09 |
Message-ID: | 529D1ECD.2070504@hogranch.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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
From: | David Johnston <polobo(at)yahoo(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Inserting boolean types as an alias? |
Date: | 2013-12-03 01:18:57 |
Message-ID: | 1386033537948-5781337.post@n5.nabble.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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.
From: | John R Pierce <pierce(at)hogranch(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Inserting boolean types as an alias? |
Date: | 2013-12-03 01:23:49 |
Message-ID: | 529D32A5.2000907@hogranch.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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
From: | Michal Politowski <mpol+pg(at)meep(dot)pl> |
---|---|
To: | |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Inserting boolean types as an alias? |
Date: | 2013-12-03 10:36:53 |
Message-ID: | 20131203103653.GA27846@meep.pl |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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.