4

everybody using mysql knows:

SELECT SQL_CALC_FOUND_ROWS ..... FROM table WHERE ...  LIMIT 5, 10;

and right after run this :

SELECT FOUND_ROWS();

how do i do this in postrgesql? so far, i found only ways where i have to send the query twice...

3 Answers 3

5

No, there is not (at least not as of July 2007). I'm afraid you'll have to resort to:

BEGIN ISOLATION LEVEL SERIALIZABLE;

SELECT id, username, title, date FROM posts ORDER BY date DESC LIMIT 20;
SELECT count(id, username, title, date) AS total FROM posts;

END;

The isolation level needs to be SERIALIZABLE to ensure that the query does not see concurrent updates between the SELECT statements.

Another option you have, though, is to use a trigger to count rows as they're INSERTed or DELETEd. Suppose you have the following table:

CREATE TABLE posts (
    id      SERIAL PRIMARY KEY,
    poster  TEXT,
    title   TEXT,
    time    TIMESTAMPTZ DEFAULT now()
);

INSERT INTO posts (poster, title) VALUES ('Alice',   'Post 1');
INSERT INTO posts (poster, title) VALUES ('Bob',     'Post 2');
INSERT INTO posts (poster, title) VALUES ('Charlie', 'Post 3');

Then, perform the following to create a table called post_count that contains a running count of the number of rows in posts:

-- Don't let any new posts be added while we're setting up the counter.
BEGIN;
LOCK TABLE posts;

-- Create and initialize our post_count table.
SELECT count(*) INTO TABLE post_count FROM posts;

-- Create the trigger function.
CREATE FUNCTION post_added_or_removed() RETURNS TRIGGER AS $$
    BEGIN
        IF TG_OP = 'DELETE' THEN
            UPDATE post_count SET count = count - 1;
        ELSIF TG_OP = 'INSERT' THEN
            UPDATE post_count SET count = count + 1;
        END IF;
        RETURN NULL;
    END;
$$ LANGUAGE plpgsql;

-- Call the trigger function any time a row is inserted.
CREATE TRIGGER post_added_or_removed_tgr
    AFTER INSERT OR DELETE
    ON posts
    FOR EACH ROW
    EXECUTE PROCEDURE post_added_or_removed();

COMMIT;

Note that this maintains a running count of all of the rows in posts. To keep a running count of certain rows, you'll have to tweak it:

SELECT count(*) INTO TABLE post_count FROM posts WHERE poster <> 'Bob';

CREATE FUNCTION post_added_or_removed() RETURNS TRIGGER AS $$
    BEGIN
        -- The IF statements are nested because OR does not short circuit.
        IF TG_OP = 'DELETE' THEN
            IF OLD.poster <> 'Bob' THEN
                UPDATE post_count SET count = count - 1;
            END IF;
        ELSIF TG_OP = 'INSERT' THEN
            IF NEW.poster <> 'Bob' THEN
                UPDATE post_count SET count = count + 1;
            END IF;
        END IF;
        RETURN NULL;
    END;
$$ LANGUAGE plpgsql;
Sign up to request clarification or add additional context in comments.

Comments

4

There is a simple way, but keep in mind, that following COUNT(*) aggr function will be applied to all rows returned after where and before limit/offset (may be costy)

SELECT id, "count" (*) OVER () AS cnt FROM objects WHERE id > 2 OFFSET 50 LIMIT 5

Comments

0

No, PostgreSQL doesn't try to count all relevant results when you only need 10 results. You need a seperate COUNT to count all results.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.