英文:
Querying xmin column in Postgres yields xid in query
问题
我正在尝试在PostgreSQL中按顺序获取第一个与xmin列上的大于或等于查询匹配的记录，该查询与递增整数进行比较。我的查询在执行时如下所示：
SELECT xmin, column1, column2, column3 FROM records WHERE xmin >= $1 LIMIT 1 ;
然后在调用时，我通过标准的sql.QueryRow函数将参数1传递给查询。
然而，我期望得到一行返回，但实际上返回了以下错误。
pq: operator does not exist: xid >= unknown
这里有两个问题：我无法弄清楚xid是从哪里来的，因为我从未查询过它，而且我也无法弄清楚为什么占位符值没有被正确添加。参数在调用时被传递给查询，它不是nil或其他任何值，但是Postgres引擎不喜欢我的查询。
英文:
I am trying to sequentially grab the first record that matches the greater than or equal query on the xmin column compared to an incrementing integer in PostgreSQL. My query looks like this at execution time:
SELECT xmin, column1, column2, column3 FROM records WHERE xmin >= $1 LIMIT 1 ;
And then at call time, I pass the args 1 to the query through the standard sql.QueryRow function.
However, I expect to be getting back a row, instead I'm returned the following error.
pq: operator does not exist: xid >= unknown
Two things here: I can't figure out where xid is coming from since I never query for it, and I can't figure out why the placeholder value is not being added correctly either. The argument is getting passed to the query at call time, it's not nil or anything, but the Postgres engine doesn't like my query.
答案1
得分: 2
如果你使用 'blah' >= 4，错误信息会显示类似于 operator does not exist: text >= integer。xid 是一个类型而不是一个列。它不是一个整数，不能直接与整数进行比较。
如果你真的想这样做，可以使用 xid::text::bigint >= $1，但请确保你理解事务环绕的影响。
让我再重复一遍最后一点 - 在你开始尝试进行数值比较之前，请去了解一下 PostgreSQL 事务 ID 的工作原理。
英文:
If you had 'blah' >= 4 the error would say something like operator does not exist: text >= integer. The xid is a type not a column. It isn't an integer and can't directly be compared to one.
You can do xid::text::bigint >= $1 if you really want to but make sure you understand the implications of transaction wrap-around.
Let me repeat that last point - go away and read up about how PostgreSQL transaction IDs work before you start trying to compare them numerically.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论