How to fix this SQL query issue in postgres db?

Question

I have a SQL query that must return a list of overdue rentals and know I write this query in Postgres as below:

SELECT 
    CONCAT(customer.last_name, ', ' , customer.first_name) AS customer,
    address.phone, film.title
FROM 
    rental 
INNER JOIN 
    customer ON rental.customer_id = customer.customer_id
INNER JOIN 
    address ON customer.address_id = address.address_id
INNER JOIN 
    inventory ON rental.inventory_id = inventory.inventory_id
INNER JOIN 
    film ON inventory.film_id = film.film_id
WHERE 
    rental.return_date IS NULL
    AND rental_date + INTERVAL film.rental_duration DAY < CURRENT_DATE
LIMIT 5;

but when I run this query in pgAdmin, I get this error :

ERROR: syntax error at or near "film"
LINE 8: AND rental_date + INTERVAL film.rental_duration DAY < CU...

What is the problem?

I think, you need to give the table name of column of that rental_date — sri harsha
Does PostgreSql know such property as "CURRENT_DATE"? I suppose, you should use "now()" function there. — Alex Sham
I change to rental.rental_date But the problem was not resolved! — fardad

Georgi Raychev Georgi Raychev · Accepted Answer · 2019-01-25T07:22:17

The proper syntax for interval addition is something like this: select current_date + interval '1 day'. You are missing some apostrophes, so it wont work this way.

It should be okay if you change your line 8 to look like this:

AND rental_date + film.rental_duration < CURRENT_DATE

If rental_duration is not an integer type, go like this:

AND rental_date + film.rental_duration::int < CURRENT_DATE

If rental_date is a timestamp:

AND rental_date + interval '1 day' * film.rental_duration < CURRENT_DATE

How to fix this SQL query issue in postgres db?

1 Answers