Postgres ERROR: syntax error at or near "HOUR"

Question

I want to delete all records into Postgress table and leave only records which were created last 1 hour. I tried this:

DELETE FROM common.orders WHERE created_at < (NOW(), INTERVAL -1 HOUR);

But I get error:

[42601] ERROR: syntax error at or near "HOUR"

Do you know how I can fix this issue?

I think this post: stackoverflow.com/questions/30043431/… will answer your query — Abhinav Agrawal
– Abhinav Agrawal, Commented Oct 7, 2021 at 10:30
But I get error: [42601] ERROR: syntax error at or near "HOUR" 42601 is not documented SQLSTATE in MySQL. Moreover, there is no 42601 number on dev.mysql.com at all... And MySQL never uses double quote chars for keywords wrapping in its error messages. — Akina
– Akina, Commented Oct 7, 2021 at 11:10
I saw my mistake. The database is Postgress. I will update my question. — user1285928
– user1285928, Commented Oct 7, 2021 at 11:16

Tim Biegeleisen · Accepted Answer · 2021-10-07 11:21:54Z

2

Move the minus outside of the INTERVAL clause. Also, the comma after NOW() should be removed as well, and the value for the unit needs to be specified in single quotes

DELETE FROM common.orders WHERE created_at < NOW() - INTERVAL '1 HOUR';

edited Oct 7, 2021 at 11:21

user330315

answered Oct 7, 2021 at 10:29

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

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

1 Comment

jarlh Over a year ago

Just a note, the ANSI SQL syntax is INTERVAL '1' HOUR.

Collectives™ on Stack Overflow

Postgres ERROR: syntax error at or near "HOUR"

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related