go-postgres-pagination-example icon indicating copy to clipboard operation
go-postgres-pagination-example copied to clipboard

Incorrect query when using uuid and created_time

Open alsofr opened this issue 4 years ago • 0 comments

This part of the code is incorrect.

queryBuilder = queryBuilder.Where(sq.LtOrEq{
	"created_time": createdCursor,
})
queryBuilder = queryBuilder.Where(sq.Lt{
	"id": paymentID,
})

I'm not sure how it should look like with the squirrel query framework, but in SQL you can use the tuple syntax

(created_time,id) < ('2020-05-24 21:27:10' :: timestamp, 'fffe3477-1710-45c4-b554-b539a9ee8fa7') 

or something like

WHERE ...
AND (
  created_time < '2020-05-24 21:27:10'
  OR
  created_time = '2020-05-24 21:27:10' AND id < 'fffe3477-1710-45c4-b554-b539a9ee8fa7'

Reference: https://dba.stackexchange.com/questions/267794/how-to-do-pagination-with-uuid-v4-and-created-time-on-concurrent-inserted-data

alsofr avatar Nov 02 '21 20:11 alsofr