Re: How to check a table content efficiently? With LIMIT and OFFSET?

Hi,

That's my solution candidate:

CREATE OR REPLACE FUNCTION isnotempty() RETURNS boolean AS '
  SELECT (count(*) = 1)
  FROM (SELECT osm_id FROM planet_osm_point LIMIT 1 OFFSET 100000) tmp
' LANGUAGE SQL;

Yours, Stefan

2011/5/28 Stefan Keller <sfkeller@gmail.com>:
> Hi,
>
> I’d like to monitor a table in a read-only Postgres database by
> writing a PL/pgSQL function.
> The database gets periodically overwritten by a mirroring loading process.
> The success criteria is, that the table contains at least some records
> (actually at least more than 100000).
>
> The first idea which comes to one’s mind is probably a count(*):
>
> SELECT CASE WHEN (count(*) > 10000) THEN 'yes' ELSE 'no' END
> FROM planet_osm_point;
>
> But my goal is to consume as few Postgres server resources as possible.
> So my idea is to retrieve an arbitrary record, like this:
>
> SELECT CASE WHEN (osm_id::text > '') THEN 'yes' ELSE 'no' END
> FROM planet_osm_point LIMIT 1 OFFSET 1000000;
>
> Is there anyone having an even better  better idea?
>
> Yours, Stefan
>