How to use LIMIT clause with postgres and Hibernate/Spring

Question

I have this native query that won't work in my DAO

@Query(
            nativeQuery = true,
            value = "DELETE FROM products_in_carts WHERE cart_id =?1 AND product_name = ?2 LIMIT= 1"
    )
    void removeProduct(long id, String productName);

Which returns: org.postgresql.util.PSQLException: ERROR: syntax error at or near "LIMIT"

I tried with OFFSET as suggested in some other questions but won't work either

Answer 1

That is because, as i understand the documentation correctly, postgres does not support using LIMIT in delete statement. Therefore you can try using a workaround to achieve your requirement. For example getting the rows to delete by using a subquery inside the delete statement. As I do not know your table design I am using the CTID here from postgres to identify the row to delete.

Taken from the documentation (https://www.postgresql.org/docs/8.2/ddl-system-columns.html)

ctid The physical location of the row version within its table. Note that although the ctid can be used to locate the row version very quickly, a row's ctid will change each time it is updated or moved by VACUUM FULL. Therefore ctid is useless as a long-term row identifier. The OID, or even better a user-defined serial number, should be used to identify logical rows.

For example (not tested):

@Query(
        nativeQuery = true,
        value = "DELETE FROM products_in_carts WHERE ctid in (select ctid from product cart_id =?1 AND product_name = ?2 LIMIT= 1)"
)
void removeProduct(long id, String productName);