How to return 0 if table empty, 1 otherwise

In postgreSQL, ho开发者_StackOverfloww can i return a table containing 0 if my table is empty and a table containing 1 if my table has rows?

I need to do it in SQL, not using any other language

Use:

SELECT CASE 
         WHEN EXISTS (SELECT * FROM foo LIMIT 1) THEN 1
         ELSE 0 
       END

EDIT: Added LIMIT 1 to speed up query.

Might be a hack, but it works.

SELECT count(*) FROM (SELECT 1 FROM table LIMIT 1) AS t;

• The 1 selected in the sub-query could be whatever, it is just a place holder.
• The LIMIT 1 should make the sub-query very fast, regardless of table-size.

Edit: Rewrote a bit. Previous use of LIMIT was wrong (didn't help on large tables as I intended).

Try:

select sign(count(*)) from mytable

maybe this is what you are looking for?

select min( c ) from (
select count(*) c
from mytab
union
select 1
from mytab
having count(*) > 1 )

SELECT, COUNT and LIMIT should do it.

-- see below SELECT COUNT(*) FROM X LIMIT 1

Edit: This doesn't work in Postgres (8.x at least). Please see the comments and here: http://postgresql.1045698.n5.nabble.com/window-function-count-and-limit-td3233588.html

Happy SQL'ing.

You can put this request in a stored procedure, with the table name as parameter :

CREATE OR REPLACE FUNCTION isEmpty(tableName text, OUT zeroIfEmpty integer) AS
$func$
BEGIN
EXECUTE format('SELECT COALESCE ((SELECT 1 FROM %s LIMIT 1),0)', tableName)
INTO zeroIfEmpty;
END
$func$ LANGUAGE plpgsql;

Then run this function like this :

SELECT * FROM isEmpty('my_table_name');

So you can call it with any of your table's name

I don't think that's possible using nothing but SQL. Why can't you use any other language?