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

Thanks this is exactly what I need it.

Fernando

Tom Lane wrote: