PostgreSQL Prepared Queries ( Statements) For Beginners
When we write SQL queries, we often run the same query again and again, only changing the values.
PostgreSQL has a feature that helps with this problem called prepared queries (also called prepared statements).
In this article, we will learn:
- What prepared queries are
- Why they are useful
- How to use them in PostgreSQL
- Real examples you can try yourself
No complex words. No heavy theory. Just clear examples.
What Is a Prepared Query?
A prepared query is an SQL query that is:
- Created once
- Saved by PostgreSQL
- Executed many times with different values
Instead of sending the full SQL text every time, we send values only.
Think of it like this:
“PostgreSQL, remember this query. I will give you different values later.”
Why Use Prepared Queries?
Prepared queries give us three big benefits:
Better Performance
PostgreSQL prepares the query plan once.
Later executions are faster because PostgreSQL does not need to plan again.
Better Security
Prepared queries protect against SQL injection.
User input is treated as data, not SQL code.
Cleaner Code
Your SQL becomes easier to read and reuse.
Simple Table for Our Examples
Let’s create a table called users.
CREATE TABLE users (
id SERIAL PRIMARY KEY,
name TEXT,
email TEXT,
age INT
);Insert some data:
INSERT INTO users (name, email, age)
VALUES
('Alice', 'alice@test.com', 25),
('Bob', 'bob@test.com', 30),
('Charlie', 'charlie@test.com', 35);Example 1: Using PREPARE and EXECUTE
Step 1: Prepare the Query
We prepare a query with placeholders ($1, $2, etc.).
PREPARE get_user_by_age (INT) AS
SELECT name, email
FROM users
WHERE age > $1;Here:
get_user_by_ageis the name of the prepared queryINTis the data type for$1
Step 2: Execute the Prepared Query
Now we run the query with a value.
EXECUTE get_user_by_age(28);Result:
- Bob
- Charlie
We can run it again with a different value:
EXECUTE get_user_by_age(32);Result:
- Charlie
The SQL stays the same. Only the value changes.
Example 2: Prepared Query With Multiple Values
Let’s prepare a query with two inputs.
PREPARE get_user_by_name_and_age (TEXT, INT) AS
SELECT *
FROM users
WHERE name = $1 AND age > $2;Execute it:
EXECUTE get_user_by_name_and_age('Alice', 20);Result: Alice’s row
Example 3: Prepared Queries and SQL Injection
Bad Way (Not Safe)
SELECT * FROM users WHERE email = 'user_input';If user_input contains SQL code, your database is in danger.
Good Way (Safe)
PREPARE get_user_by_email (TEXT) AS
SELECT *
FROM users
WHERE email = $1;Execute:
EXECUTE get_user_by_email('alice@test.com');PostgreSQL treats the value as data only, not SQL code.
Example 4: Deallocating a Prepared Query
Prepared queries stay in memory until:
- The session ends, or
- You remove them manually
To remove one:
DEALLOCATE get_user_by_age;Or remove all prepared queries:
DEALLOCATE ALL;Prepared Queries in Real Applications
In real apps, you often don’t write PREPARE manually.
Libraries and drivers do this for you:
- Java (JDBC PreparedStatement)
- Python (psycopg)
- Node.js (pg)
But PostgreSQL still uses the same idea inside.
Knowing this helps you:
- Write faster queries
- Debug performance issues
- Understand how your app talks to the database
When Should You Use Prepared Queries?
Use them when:
- You run the same query many times
- You use user input
- Performance matters
You may skip them when:
- You run a query only once
- The query is very simple and not repeated
Final Thoughts
Prepared queries are simple and powerful.
They help you:
- Run queries faster
- Write safer SQL
- Build better applications
I’ve been an Architect, a DevOps, a DataOps, and a DBA. I’m focused on databases, real-time data and streaming technologies. I work with Apache Kafka, RabbitMQ, Elastic Stack and PostgreSQL/MySQL. After hours I love to share my knowledge and experience. I’m founder of DataOps Poland group, it’s an exchange place of experience, concepts and ideas from Data Engineering world.
