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
– sri harsha, Commented Jan 25, 2019 at 7:16
Does PostgreSql know such property as "CURRENT_DATE"? I suppose, you should use "now()" function there. — Alex Sham
– Alex Sham, Commented Jan 25, 2019 at 7:17
I change to rental.rental_date But the problem was not resolved! — fardad
– fardad, Commented Jan 25, 2019 at 7:22

Georgi Raychev · Accepted Answer · 2019-01-25 07:39:10Z

1

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

edited Jan 25, 2019 at 7:39

answered Jan 25, 2019 at 7:22

Georgi Raychev

1,3441 gold badge13 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

fardad Over a year ago

my rental_duration is short type and rental_date is Timestamp type

fardad Over a year ago

know show error operator does not exist: timestamp without time zone + integer LINE 8: AND rental.rental_date + film.rental_duration::INT < CUR...

Georgi Raychev Over a year ago

Updated the answer.

Collectives™ on Stack Overflow

How to fix this SQL query issue in postgres db?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related