Format function for safe formatting in PostgreSQL - Mini Project: Build & Apply

Using the FORMAT Function for Safe SQL Queries in PostgreSQL

📖 Scenario: You are managing a small library database. You want to create SQL queries that safely insert and display book information without risking errors or SQL injection.

🎯 Goal: Build a PostgreSQL query using the FORMAT function to safely format strings and variables in SQL statements.

📋 What You'll Learn

Create a variable with a book title and author name

Create a variable with a book's publication year

Use the FORMAT function to safely combine these variables into a SQL INSERT statement

Use the FORMAT function to safely create a SELECT statement that filters books by author

💡 Why This Matters

🌍 Real World

Database developers often need to build SQL queries dynamically while avoiding errors and security risks like SQL injection. Using FORMAT with placeholders helps keep queries safe and clean.

💼 Career

Knowing how to safely format SQL queries is essential for backend developers, database administrators, and data engineers to maintain secure and reliable database applications.

1

Create variables for book title and author

Create two variables called book_title and author_name and assign them the exact values 'The Great Gatsby' and 'F. Scott Fitzgerald' respectively.

PostgreSQL

# Create variables book_title and author_name with given values
# Your code here

Need a hint?

Use the := operator to assign values to variables in PostgreSQL.

2

Create a variable for publication year

Create a variable called pub_year and assign it the exact value 1925.

PostgreSQL

book_title := 'The Great Gatsby';
author_name := 'F. Scott Fitzgerald';
# Create variable pub_year with value 1925
# Your code here

Need a hint?

Assign the integer 1925 to the variable pub_year.

3

Use FORMAT to create a safe INSERT statement

Use the FORMAT function to create a variable called insert_query that safely formats an INSERT SQL statement for a table named books with columns title, author, and year. Use %L for literal values and include the variables book_title, author_name, and pub_year in that order.

PostgreSQL

book_title := 'The Great Gatsby';
author_name := 'F. Scott Fitzgerald';
pub_year := 1925;
-- Create insert_query using FORMAT with %L placeholders
-- Your code here

Need a hint?

Use FORMAT with %L to safely insert literal values into the SQL string.

4

Use FORMAT to create a safe SELECT statement filtering by author

Use the FORMAT function to create a variable called select_query that safely formats a SELECT SQL statement to get all columns from books where the author matches the variable author_name. Use %L for the author literal.

PostgreSQL

book_title := 'The Great Gatsby';
author_name := 'F. Scott Fitzgerald';
pub_year := 1925;
insert_query := FORMAT('INSERT INTO books (title, author, year) VALUES (%L, %L, %L);', book_title, author_name, pub_year);
-- Create select_query using FORMAT to filter by author_name
-- Your code here

Need a hint?

Use FORMAT with %L to safely include the author name in the WHERE clause.