CODEWITHSUNDEEP
sqlpostgresql
Spawn
Spawn

Reputation: 181

How to return 0 if table empty, 1 otherwise

In postgreSQL, how 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

Upvotes: 14

Views: 33804

Answers (7)

Julien Feniou
Julien Feniou

Reputation: 1004

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

Upvotes: 0

GendoIkari
GendoIkari

Reputation: 11914

Use:

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

EDIT: Added LIMIT 1 to speed up query.

Upvotes: 23

user359040
user359040

Reputation:

Try:

select sign(count(*)) from mytable

Upvotes: 4

plundra
plundra

Reputation: 19222

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).

Upvotes: 14

user166390
user166390

Reputation:

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.

Upvotes: 0

Randy
Randy

Reputation: 16677

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 )

Upvotes: 0

Tmas
Tmas

Reputation: 55

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

Upvotes: -10

Related Questions