Prepared Statements in PostgreSQL: A Complete Guide

Prepared statements are a feature in PostgreSQL that can improve performance and security when executing repetitive SQL queries.

By creating a prepared statement, the server can parse and plan the query once and reuse it multiple times, leading to faster execution. Prepared statements also help in preventing SQL injection attacks by safely handling user input.

In this article, we’ll explore prepared statements in PostgreSQL, discuss how to create and use them, and cover other important considerations such as query planning and cleaning up statements.

Continue reading

3 Quick Examples of SQL Subqueries in the WHERE Clause

SQL subqueries are like little helpers that fetch data for our main query. They’re super handy when we need to filter our results based on some other data in our database.

Probably the most common placement of a subquery in SQL is in the WHERE clause. Placing a subquery in the WHERE clause allows us to compare a column against a set of rows that match a certain criteria.

Let’s look at three quick examples of subqueries in the WHERE clause.

Continue reading

Fix “Cannot use an aggregate or a subquery in an expression used for the group by list of a GROUP BY clause” in SQL Server (Error 144)

If you’re getting an error that reads something like “Cannot use an aggregate or a subquery in an expression used for the group by list of a GROUP BY clause.” in SQL Server, it looks like you’re trying to use either an aggregate function or a subquery in the GROUP BY clause.

We can’t use aggregates or subqueries in the GROUP BY clause.

To fix this issue, remove any aggregate functions or subqueries from your GROUP BY clause.

Continue reading

Understanding the RID Lookup in SQL Server: How It Affects Performance

If you’ve spent any time working with heaps in SQL Server, you may have seen the RID lookup in the query plan. And if you haven’t, chances are it’s because you either didn’t look at the query plan, or the queries used full table scans.

In any case, it pays to understand what a RID lookup is and how it affects query performance. If we ever see RID lookup in the query plan, it could be a good opportunity to see if we can make changes that will improve performance.

Continue reading

Aggregating Data in SQL with the GROUP BY and HAVING Clauses

When writing queries in SQL, we sometimes need to summarize data based on specific criteria. Fortunately SQL makes this easy to achieve with the GROUP BY and HAVING clauses. These two clauses allow us to aggregate data and filter the results based on group conditions.

More specifically, GROUP BY allows us to group the results, and HAVING is an optional clause that we can use to filter the results based on certain conditions.

Below is an example of where we can use these two clauses to aggregate data and filter it based on certain criteria.

Continue reading

Understanding PostgreSQL’s FILTER Clause

When using aggregation functions in PostgreSQL, we have the option of including a FILTER clause. This clause allows us to narrow down – or filter – the rows that are fed to the aggregate function.

This can be be a handy way to provide functionality that we might otherwise need to use a CASE statement or perhaps a CTE.

In this article, we’ll take a look at PostgreSQL’s FILTER clause, and see how it can simplify our SQL queries by replacing CASE statements with more concise code.

Continue reading