How Can I Efficiently Check for Row Existence in PL/pgSQL?

PL/pgSQL: Efficiently check if a row exists

In PL/pgSQL, verifying the existence of a row in a table is a common task. You try to use a SELECT query to retrieve an integer into a Boolean value, but this method is inefficient and error-prone. Here's a more efficient and concise solution:

The most straightforward way is to use the EXISTS operator. EXISTS returns true if at least one matching row exists in the subquery, false otherwise. This provides a clear and concise way of checking for the existence of a row:

<code class="language-sql">IF EXISTS (SELECT FROM people p WHERE p.person_id = my_person_id) THEN
  -- 执行某些操作
END IF;</code>

Advantages of EXISTS:

• Simpler and shorter: This is the most direct and readable method.
• Faster: EXISTS can stop processing after a matching row is found, minimizing query time, especially in large tables.
• Irrespective of the SELECT list: The SELECT list in a subquery can be empty, since only the presence of matching rows matters.

Comparison with COUNT:

Your original approach using COUNT requires scanning all qualifying rows to determine their count. This can be less efficient, especially if indexes are present. On the other hand, EXISTS can terminate as soon as the first matching row is found.

Note: If you need a count of matching rows, use COUNT with a condition that limits the result to one row, for example:

<code class="language-sql">IF (SELECT COUNT(*) FROM people p WHERE p.person_id = my_person_id) > 0 THEN
  -- 执行某些操作
END IF;</code>

The above is the detailed content of How Can I Efficiently Check for Row Existence in PL/pgSQL?. For more information, please follow other related articles on the PHP Chinese website!