Creating Temporary Tables for Analytics

Temporary tables in PostgreSQL are tables created for short-term data storage and are automatically deleted after the session or transaction ends (depending on the settings). They're super handy for simplifying complex operations and making queries run faster.

Imagine you're a chef in a restaurant with three-star dishes. You wouldn't keep a separate table in the kitchen just for the ingredients of one salad, right? That's exactly what temporary tables are for: a temporary workspace — prep, use, clean up.

Advantages:

• Data isolation: a temporary table is only available inside the current session or transaction. No one but you can mess with its contents.
• Efficiency: they let you save intermediate results so you don't have to rerun heavy queries.
• Cleanliness: once you're done, temporary tables are deleted automatically.

Syntax for Creating Temporary Tables

PostgreSQL gives you a simple and convenient way to create temporary tables using the CREATE TEMP TABLE command.

General syntax:

CREATE TEMP TABLE table_name (
    column_name1 data_type constraints,
    column_name2 data_type constraints,
    ...
);

• TEMP or TEMPORARY — keyword that marks the table as temporary.
• table_name — the table's name. You can pick whatever, but it's better to use meaningful names.
• column_name — the name of the column.
• data_type — the column's data type (INTEGER, TEXT, DATE, etc.).
• constraints — constraints, like NOT NULL, UNIQUE, etc.

Example of creating a simple temporary table:

CREATE TEMP TABLE temp_sales (
    id SERIAL PRIMARY KEY,
    product_name TEXT NOT NULL,
    sale_date DATE NOT NULL,
    amount NUMERIC(10, 2) NOT NULL
);

• Here we're creating a temporary table to store sales data.
• The id field auto-generates an ID for each row.
• The amount field stores a value with decimal precision (like 1234.56).

Examples of Using Temporary Tables

Example 1: Saving intermediate data for analysis

Imagine we have an orders table where all orders are stored. We need to figure out the total sales by product category. Instead of running a heavy query, we can save the data in a temporary table.

Let's create a temporary table:

CREATE TEMP TABLE temp_category_sales (
    category TEXT,
    total_sales NUMERIC(10, 2)
);

Fill the temporary table:

INSERT INTO temp_category_sales
SELECT category, SUM(amount)
FROM orders
GROUP BY category;

Now you can use the temporary table, for example, to get the top 3 categories:

SELECT *
FROM temp_category_sales
ORDER BY total_sales DESC
LIMIT 3;

Example 2: Joining data from different tables

Let's say we have two tables: students (info about students) and enrollments (enrollment records). We want to gather their enrollments in one place.

Let's create a temporary table:

CREATE TEMP TABLE temp_student_enrollments (
    student_id INTEGER,
    student_name TEXT,
    course_id INTEGER,
    enrollment_date DATE
);

Joining the data:

INSERT INTO temp_student_enrollments
SELECT s.id, s.name, e.course_id, e.enrollment_date
FROM students s
JOIN enrollments e ON s.id = e.student_id;

Now you can easily analyze this table, for example, count the number of enrollments for each student:

SELECT student_name, COUNT(course_id) AS enrollment_count
FROM temp_student_enrollments
GROUP BY student_name
ORDER BY enrollment_count DESC;

Temporary Tables in Transactions

You can also limit temporary tables to a transaction by using the ON COMMIT keyword.

ON COMMIT options:

• ON COMMIT DELETE ROWS: clears the temporary table after the transaction ends.
• ON COMMIT PRESERVE ROWS: keeps the data after the transaction ends.
• ON COMMIT DROP: drops the table after the transaction ends.

Example:

CREATE TEMP TABLE temp_analysis (
    category TEXT,
    total_sales NUMERIC(10, 2)
) ON COMMIT DROP;

In this case, the temporary table will disappear as soon as the transaction is done.

Practical Value and Use Cases

Temporary tables are perfect for real-world projects. Let's look at a couple of situations:

Use cases:

1. Complex business reports: you can run complex calculations multiple times, saving intermediate results.
2. Data prep for ETL: temporary tables are often used during extract, transform, and load (ETL) processes.
3. Query development and testing: use temporary tables to isolate data while debugging queries.

For example, if you're building a sales report, temporary tables help you save intermediate results like total revenue or trends by region, without messing with the main database structure.

Features and Common Mistakes

Working with temporary tables is almost always a blast, but there are a few things to keep in mind:

• Access limitation: temporary tables are only visible in the current session or transaction. If you end the session, the tables are gone.
• Unique name errors: you can't create a temporary table with a name that's already taken by another table in the same session.
• Data loss: make sure you grab the data you need from the temporary table before ending the session.
• Performance: if a temporary table has a ton of rows, it might eat up a lot of RAM.