Fix "function datediff/dateadd/ifnull does not exist" in PostgreSQL
ERROR: function datediff(date, date) does not exist
Postgres has no function with that name that accepts those argument types. Most often it's a function from MySQL, SQL Server or SQLite. Postgres can do the same job, it just spells it differently.
Why it happens
- Date functions from other databases: DATEDIFF, DATEADD, DATE_FORMAT, strftime, GETDATE.
- NULL helpers from other databases: IFNULL, ISNULL, NVL. Postgres uses COALESCE.
- String helpers like GROUP_CONCAT or LEN. Postgres uses string_agg and length.
- A real Postgres function called with the wrong argument types, like SUM on a text column.
How to fix it
1. DATEDIFF: subtract the dates
Fails
SELECT c.name, DATEDIFF(o.order_date, c.signup_date) AS days_to_order FROM customers c JOIN orders o ON o.customer_id = c.id;
Works
SELECT c.name, o.order_date - c.signup_date AS days_to_order FROM customers c JOIN orders o ON o.customer_id = c.id;
In Postgres, date minus date gives the number of days as an integer. For timestamps you get an interval; wrap it in EXTRACT(EPOCH FROM ...) to get seconds.
2. DATEADD: add days with +
Fails
SELECT id, order_date, DATEADD('day', 30, order_date) AS due_date
FROM orders;Works
SELECT id, order_date, order_date + 30 AS due_date FROM orders;
date + integer adds that many days and stays a date. For months or hours use an interval: order_date + INTERVAL '1 month'. SQL Server style DATEADD(day, 30, ...) fails earlier with column "day" does not exist.
3. IFNULL / ISNULL / NVL: use COALESCE
Fails
SELECT full_name, IFNULL(email, 'no email') AS email FROM crm_contacts;
Works
SELECT full_name, COALESCE(email, 'no email') AS email FROM crm_contacts;
COALESCE returns the first value that isn't NULL and takes any number of arguments, like COALESCE(email, phone, 'no contact').
Try it yourself
This is the broken query, running on real PostgreSQL in your browser. Press Run to see the error explained, then fix it.
Starting Postgres in your browser…
Real Postgres in your browser. Edit the query and run it, or open it in the full playground.
Practice so it doesn't happen in an interview
- Days to first purchaseCTE · MIN · date math
- Fast convertersJOIN + date difference
- Filling in missing managersNULL · COALESCE
- Monthly spendto_char · GROUP BY month