Pl/pgsql checking if a row exists

sql

performance

best-practices

database-optimization

byAlex Kataev·Jan 12, 2025

Confidently ascertain if a row is present with a sleek PL/pgSQL block using the EXISTS clause:

IF EXISTS (SELECT 1 FROM your_table WHERE your_condition) THEN
    -- Logic for existing row (aka party time 🥳)
ELSE
    -- Logic for non-existing row (Or not...)
END IF;

The mighty EXISTS offers enhanced performance by stopping at the first found row, yielding a more efficient existence check.

EXISTS vs. COUNT: The showdown

So why call in EXISTS instead of the popular COUNT(*)? It all boils down to performance.

If you instruct PostgreSQL to do a COUNT, it takes a deep breath and dives into the entire table, counting all rows that match your condition. This, my friend, is like counting all the fish in the sea when all you need is to check if there's at least one fish left!

With EXISTS, PostgreSQL puts the brakes on as soon as the first matching row is found, saving it from extra laps in the sea of data.

The EXISTS advantage

EXISTS isn't just speedy—it has a few more tricks up its sleeve:

Smart Exit Strategy: EXISTS cleverly leverages PostgreSQL's query planner to exit quickly as soon as the first matching row makes an entrance.
Unique-Column Champs: EXISTS shines brightly under the spotlight of unique indexes and primary keys. Whenever you're dealing with unique rows, EXISTS is your go-to move.
Simpler Logic, Happier Code: EXISTS keeps your code clean and readable. It cares about whether there's any data, not how many. Your future self will thank you for this.

Pitfalls: Dodging the bullets

Heads up! Dodging potential pitfalls in the database world can save your script (and sanity):

Excessive Conditions: Keep your WHERE clause short and sweet. The fewer conditions, the faster your query.
COUNT Cravings: Resist the urge to use COUNT(*) if EXISTS does the trick. It's not just slower—it makes your code harder to read.
One-Size-Fits-All Syndrome: While EXISTS is fantastic, it may not be the best fit for every situation. Avoid boxing your queries—different situations call for different strategies.

The Other Side: Alternatives to EXISTS

Just for kicks, let's explore other options you might want to add to your toolbox for those 'just in case' moments:

LIMIT the Scan: When you need to collect row data but want to avoid searching high and low in your table, adding LIMIT 1 to your query can simulate EXISTS, while actually grabbing the row.
Joining Forces: In multifaceted queries, consider joining tables and placing existence checks in the JOIN conditions. It's sneakier, but just as valid.

Hooked on Practicality: examples

EXISTS isn't just about simplicity—it can handle complexity too. Let's look at some real-world examples:

Cross-table checks

IF EXISTS (
    SELECT 1
    FROM author
    WHERE EXISTS (
        SELECT 1
        FROM book
        WHERE book.author_id = author.id AND book.title = 'Unseen PostgreSQL'
    )
) THEN
    -- Author found! Time to send a fan letter 💌
ELSE
    -- No author, no autographs...
END IF;

Multi-condition checks

IF EXISTS (
    SELECT 1 
    FROM employee
    WHERE start_date > CURRENT_DATE - INTERVAL '1 year' AND department = 'IT'
) THEN
    -- Fresh IT allegiance found. Send coffee! ☕️
ELSE
    -- No new geek squad members...
END IF;