Dear PostgreSQL users,

Presume you had a table of employees and the incentive payments they
received additionally to their salaries.
Now you would like to give someone the information, whether an employee
received an incentive or not, but not the exact height of the payment.
Is it possible to create a view of the table which converts the column
containing the numeric values of the incentives to a column of the data
type boolean?

Thanks in advance,
Julia

On Sat, 28 Aug 2010 21:10:33 +0200, Julia Jacobson wrote:

Of course, just convert it to the data type boolean:


mgogala=# select 5::boolean;
bool
------
t
(1 row)

Time: 74.110 ms
mgogala=# select 0::boolean;
bool
------
f
(1 row)

Time: 0.252 ms

If the numbers are float or double numbers, you can always be creative
with casts:

mgogala=# select cast(3.14 as int)::boolean;
bool
------
t
(1 row)


So, the select would look something like this:

select ename, empno,job,mgr,sal,cast(bonus as int)::boolean
from emp
where deptno=10
order by sal desc;


--
http://mgogala.byethost5.com

Julia Jacobson wrote on 28.08.2010 21:10:
select case
when bonus is not null and bonus > 0 then true
else false
end
from ...

Thomas

Thanks a lot for your answer.
Unfortunately, there is one exception where your solution doesn't work:
If the bonus is less than 1 dollar, for example 0.50 dollar,
your query would indicate that the employee didn't get a bonus.
In this case,

On Sat, 28 Aug 2010 21:41:42 +0200, Thomas Kellerer wrote:

This solution is better than mine. My solution doesn't handle null
values, despite being more elegant.



--
http://mgogala.byethost5.com

On Sat, 28 Aug 2010 21:50:10 +0200, Julia Jacobson wrote:

For all practical purposes, the employee who cannot even get twinkies
from a vending machine for the amount of his or her bonus, did not, in
fact, get a bonus, he was a victim of a cruel prank. My solution also
doesn't handle null values, which is a more significant issue.



--
http://mgogala.byethost5.com

On Sat, 28 Aug 2010 19:55:01 +0000, Mladen Gogala wrote:

Of course, that can be fixed by using the "coalesce" function, but then
the elegance is lost.



--
http://mgogala.byethost5.com

Mladen Gogala wrote:

Elegance is always lost as soon as one introduces null. That part's not
your fault.

On 28.08.2010 21:41, Thomas Kellerer wrote:
I would have suggested CASE as well. But is the NULL check really
needed? IMHO "bonus > 0" can only be true if bonus is not 0. This is
basic SQL standard, isn't it?

Btw, I am not yet familiar with PostgreSQL's security model but the
whole exercise might be moot if you cannot prevent access to the base
table for those who are only allowed to see the flag version of the
incentive.

Kind regards

robert

--
remember.guy do |as, often| as.you_can - without end
http://blog.rubybestpractices.com/

Robert Klemme wrote on 29.08.2010 11:41:
True

Just revoke the SELECT privilege on the table and grant the (only) on the view.

Regards
Thomas