Migrating hardcoded query from MYSQL to PostgreSQL

Question

Hi I have one "mysql" query that looks like this:

... WHERE (maintenanceLockTimestamp + "+ delay+" SECOND) < CURRENT_TIMESTAMP ) ) "

The thing is that, we need to move from MYSQL to PostgreSQL, but when executing the same query, I get:

Caused by: org.postgresql.util.PSQLException: ERROR: syntax error at or near "SECONDS"

I checked here: https://www.postgresqltutorial.com/postgresql-date-functions/

and it looks like the SECOND function is not present in PostgreSQL, do you know any alternative?

thanks

Gordon Linoff · Accepted Answer · 2020-07-03 12:31:24Z

2

In Postgres, you can use:

where maintenanceLockTimestamp + :delay * interval '1 second' < current_timestamp

Note the use of a parameter to avoid munging the query string with literal values.

In both databases, this is better written with the date/time arithmetic on the current_timestamp:

where maintenanceLockTimestamp < current_timestamp - ? second  -- MySQL
where maintenanceLockTimestamp < current_timestamp - :delay * interval '1 second'  -- Postgres

This allows the optimizer to use an index on maintenanceLockTimestamp for performance.

answered Jul 3, 2020 at 12:31

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

3 Comments

Manuelarte Over a year ago

what an amazing answer.... incredible. I fixed that issue, now I have another one related to the naming strategy, since my table is named in the entity with @Table(name=NodeInfo) but in postgres I have node_info. But the 'seconds'problem is working. Thanks.

Manuelarte Over a year ago

Actually I'm getting this: Thanks for your answer, I still get this: PSQLException: ERROR: operator does not exist: character varying * interval Hint: No operator matches the given name and argument types. You might need to add explicit type casts.

Gordon Linoff Over a year ago

@Manuelarte . . . You need to pass the parameter in as a number or convert to a number in Postgres -- say :delay::int * interval '1 second'.

Collectives™ on Stack Overflow

Migrating hardcoded query from MYSQL to PostgreSQL

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