Type casting with :: operator in PostgreSQL - Mini Project: Build & Apply

Type Casting with :: Operator in PostgreSQL

📖 Scenario: You are working with a PostgreSQL database that stores product information. Some numeric values are stored as text, but you need to perform calculations on them.

🎯 Goal: Learn how to convert text data to numeric types using the :: type casting operator in PostgreSQL.

📋 What You'll Learn

Create a table with text columns containing numeric values

Add a configuration variable to specify a threshold price

Write a query that casts text to numeric using the :: operator

Complete the query to filter products based on the casted numeric value

💡 Why This Matters

🌍 Real World

In real databases, numeric data is sometimes stored as text. Casting lets you convert it to numbers for calculations.

💼 Career

Database developers and analysts often need to clean and convert data types to run accurate queries and reports.

1

Create the products table with text prices

Create a table called products with two columns: product_name as TEXT and price_text as TEXT. Insert these exact rows: ('Apple', '10'), ('Banana', '5'), ('Cherry', '15').

PostgreSQL

-- Create the products table and insert data
-- Your code here

Need a hint?

Use CREATE TABLE to define the table and INSERT INTO to add rows.

2

Set a price threshold variable

Create a variable called price_threshold and set it to 8 using a WITH clause for use in the query.

PostgreSQL

CREATE TABLE products (
    product_name TEXT,
    price_text TEXT
);

INSERT INTO products (product_name, price_text) VALUES
('Apple', '10'),
('Banana', '5'),
('Cherry', '15');

-- Define price_threshold variable
-- Your code here

Need a hint?

Use a WITH clause to create a named subquery that holds the threshold value.

3

Write a query casting price_text to integer

Write a SELECT query that selects product_name and casts price_text to INTEGER using the :: operator as price from the products table.

PostgreSQL

CREATE TABLE products (
    product_name TEXT,
    price_text TEXT
);

INSERT INTO products (product_name, price_text) VALUES
('Apple', '10'),
('Banana', '5'),
('Cherry', '15');

WITH price_threshold AS (SELECT 8 AS value)
-- Select product_name and cast price_text to integer as price
-- Your code here

Need a hint?

Use price_text::INTEGER to convert the text to a number.

4

Filter products with price above threshold

Complete the query by joining with price_threshold and adding a WHERE clause to select only products where the casted price is greater than price_threshold.value.

PostgreSQL

CREATE TABLE products (
    product_name TEXT,
    price_text TEXT
);

INSERT INTO products (product_name, price_text) VALUES
('Apple', '10'),
('Banana', '5'),
('Cherry', '15');

WITH price_threshold AS (SELECT 8 AS value)
SELECT product_name, price_text::INTEGER AS price
FROM products
-- Join with price_threshold and filter price > price_threshold.value
-- Your code here

Need a hint?

Use a comma join with price_threshold and a WHERE clause to filter.