Обсуждение: Conditional on Select List

Fernando,

the "IF" function is called

CASE
WHEN condition THEN result
      [WHEN ...]
      [ELSE result]
 END

read about it at
http://www.postgresql.org/docs/8.3/static/functions-conditional.html

best wishes,

Harald


On Tue, May 13, 2008 at 5:52 PM, Fernando <fernando@ggtours.ca> wrote:
>
>  Is it possible to do this?
>
>  SELECT IF(COUNT(colname) > 0, TRUE, FALSE) AS colname FROM table;
>
>  What I want is to return a boolean, but when I tried SELECT
> COUNT(colname)::BOOLEAN FROM table; it says it cannot cast bigint to
> boolean.
>
>  Is there such IF function or do I have to create my own.
>
>  Thank you.
>



--
GHUM Harald Massa
persuadere et programmare
Harald Armin Massa
Spielberger Straße 49
70435 Stuttgart
0173/9409607
fx 01212-5-13695179
-
EuroPython 2008 will take place in Vilnius, Lithuania - Stay tuned!

Fernando wrote:
> Is it possible to do this?
>
> SELECT IF(COUNT(colname) > 0, TRUE, FALSE) AS colname FROM table;
>
> What I want is to return a boolean, but when I tried SELECT
> COUNT(colname)::BOOLEAN FROM table; it says it cannot cast bigint to
> boolean.
>
> Is there such IF function or do I have to create my own.

Look up CASE in the docs..

Joshua D. Drake

On Tue, May 13, 2008 at 11:52:24AM -0400, Fernando wrote:
> Is it possible to do this?
>
> SELECT IF(COUNT(colname) > 0, TRUE, FALSE) AS colname FROM table;
>
> What I want is to return a boolean, but when I tried SELECT
> COUNT(colname)::BOOLEAN FROM table; it says it cannot cast bigint to
> boolean.

Why not just do something like this?

  SELECT COUNT(colname) > 0 AS colname FROM table;

If you want a real conditional statement, CASE is probably what you
want:

  SELECT CASE WHEN COUNT(colname) > 0 THEN TRUE ELSE FALSE END AS colname FROM table;

Note that the ELSE clause is executed when the expression evaluates to
either NULL or FALSE, but because COUNT never returns a NULL value it
doesn't matter here.  It's also possible to have multiple WHEN clauses.


  Sam

>
> Is it possible to do this?
>
> SELECT IF(COUNT(colname) > 0, TRUE, FALSE) AS colname FROM table;
>
> What I want is to return a boolean, but when I tried SELECT
> COUNT(colname)::BOOLEAN FROM table; it says it cannot cast bigint to
> boolean.
>

How about this?  Logic
al expresses are already returned as Boolean.

Select COUNT(colname) > 0 AS colname FROM table

Fernando <fernando@ggtours.ca> writes:
> Is it possible to do this?
> SELECT IF(COUNT(colname) > 0, TRUE, FALSE) AS colname FROM table;

SELECT COUNT(colname) > 0 AS colname FROM table;

If you really like to type, you could use a CASE expression.

            regards, tom lane

On Tue, May 13, 2008 at 11:52 AM, Fernando <fernando@ggtours.ca> wrote:
>
>  Is it possible to do this?
>
>  SELECT IF(COUNT(colname) > 0, TRUE, FALSE) AS colname FROM table;

You should be able to use CASE for this.

-Doug

On Tue, May 13, 2008 at 8:52 AM, Fernando <fernando@ggtours.ca> wrote:
> SELECT IF(COUNT(colname) > 0, TRUE, FALSE) AS colname FROM table;

A rewrite of this query might do what you want:

SELECT colname_cnt > 0 AS Greaterthanzero
  FROM ( SELECT COUNT( colname ) AS colname_cnt
           FROM Table );

or

SELECT CASE COUNT( colname )
       WHEN > 0
       THEN TRUE
       ELSE FALSE
       END AS GreaterThanZero
  FROM Table;
--
Regards,
Richard Broersma Jr.

Visit the Los Angles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug

Fernando <fernando@ggtours.ca> writes: 

Is it possible to do this?
SELECT IF(COUNT(colname) > 0, TRUE, FALSE) AS colname FROM table;   

SELECT COUNT(colname) > 0 AS colname FROM table;

If you really like to type, you could use a CASE expression.
		regards, tom lane