Scheduling automated cleaning with pg_cron

pg_cron runs SQL on a timetable, like every night at 2am. What it runs is usually a clean-up function you write and test first.

Try this lesson free →

About 5–10 minutes · no sign-up needed

Example

Writing the clean-up job a schedule runs

CREATE OR REPLACE FUNCTION nightly_cleanup() RETURNS void LANGUAGE SQL AS $$ DELETE FROM orders WHERE revenue < 0 $$;
SELECT nightly_cleanup();
SELECT * FROM orders

Result:

order_id	customer_id	category	revenue	order_date
O1001	C101	Electronics	299.99	2024-03-01
O1002	C102	Clothing	49.95	2024-03-01
O1003	C101	Home	134.0	2024-03-02
O1005	C102	Beauty	22.5	2024-03-03

Step by step

RETURNS void: it changes data and gives nothing back. The DELETE is the clean-up rule.

CREATE OR REPLACE FUNCTION nightly_cleanup() RETURNS void LANGUAGE SQL AS $$ DELETE FROM orders WHERE revenue < 0 $$;

Call the function yourself before trusting a schedule with it.

CREATE OR REPLACE FUNCTION nightly_cleanup() RETURNS void LANGUAGE SQL AS $$ DELETE FROM orders WHERE revenue < 0 $$;
SELECT nightly_cleanup();

The bad row is gone. On the live database, cron.schedule() would now run it every night.

CREATE OR REPLACE FUNCTION nightly_cleanup() RETURNS void LANGUAGE SQL AS $$ DELETE FROM orders WHERE revenue < 0 $$;
SELECT nightly_cleanup();
SELECT * FROM orders;

Practice

Try each one in your head first, then open the answer.

1. Write a function nightly_cleanup() that returns void and deletes orders with revenue below 0.

CREATE OR REPLACE FUNCTION nightly_cleanup() RETURNS void LANGUAGE SQL AS $$ DELETE FROM orders WHERE revenue < 0 $$

2. nightly_cleanup() already exists. Run it, then show all orders.

SELECT nightly_cleanup(); SELECT * FROM orders

3. Write nightly_cleanup() (deletes orders with revenue below 0), run it, then show all orders.

CREATE OR REPLACE FUNCTION nightly_cleanup() RETURNS void LANGUAGE SQL AS $$ DELETE FROM orders WHERE revenue < 0 $$; SELECT nightly_cleanup(); SELECT * FROM orders

4. Write remove_blank_categories() that returns void and deletes orders where category IS NULL. Run it, then show all orders.

CREATE OR REPLACE FUNCTION remove_blank_categories() RETURNS void LANGUAGE SQL AS $$ DELETE FROM orders WHERE category IS NULL $$; SELECT remove_blank_categories(); SELECT * FROM orders

5. Write fix_categories() that returns void and sets every category to INITCAP(TRIM(category)). Run it, then show order_id and category.

CREATE OR REPLACE FUNCTION fix_categories() RETURNS void LANGUAGE SQL AS $$ UPDATE orders SET category = INITCAP(TRIM(category)) $$; SELECT fix_categories(); SELECT order_id, category FROM orders

Learn it by doing. This lesson is a short hands-on level on Bampaa: see it work, then build and write it yourself, with instant feedback.

Try this lesson free →

About 5–10 minutes · no sign-up needed