CODEWITHSUNDEEP
sqlpostgresql
fardad
fardad

Reputation: 67

How to fix this SQL query issue in postgres db?

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?

Upvotes: 0

Views: 221

Answers (1)

Georgi Raychev
Georgi Raychev

Reputation: 1334

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

Upvotes: 1

Related Questions