在ResultSet Postgres中找不到创建的列名

Question

@Query(value = "UPDATE einvoice_message AS e1 SET message = null FROM (SELECT * FROM einvoice_message 
where created < :created ORDER BY :created LIMIT :limit FOR UPDATE SKIP LOCKED) AS e2 WHERE 
e1.einvoice_message_id = e2.einvoice_message_id RETURNING e1.einvoice_message_id", nativeQuery = true)
List<EinvoiceMessage> deleteEinvoiceMessageContent(@Param("created") OffsetDateTime created, 
@Param("limit") int limit);

我在postresql中执行了这个查询，但收到了SqlExceptionHelper - The column name created was not found in this ResultSet.。应该是e1.created还是e2.created？但如果是这样，我不知道为什么，有人能解释一下这个问题的原因和解决方法吗？

这是为了更好的可读性而进行的查询：

UPDATE einvoice_message AS e1 
SET message = null 
FROM (
SELECT * 
FROM einvoice_message 
where created < :created 
ORDER BY :created LIMIT :limit 
FOR UPDATE SKIP LOCKED
) AS e2 
WHERE e1.einvoice_message_id = e2.einvoice_message_id 
RETURNING e1.einvoice_message_id

奇怪的是，我可以通过查询工具执行这个脚本，而且一切都很好。但日志中的错误清楚地表明：

SqlExceptionHelper - The column name created was not found in this ResultSet.

计划任务中出现意外错误。org.springframework.dao.InvalidDataAccessResourceUsageException：无法执行查询；

SQL [UPDATE einvoice_message AS e1 SET message = null FROM (SELECT * FROM einvoice_message where created < ? 
ORDER BY ? LIMIT ? FOR UPDATE SKIP LOCKED) AS e2 WHERE e1.einvoice_message_id = e2.einvoice_message_id RETURNING e1.einvoice_message_id];
nested exception is org.hibernate.exception.SQLGrammarException: could not execute query

我的桌子ddl:

create table einvoice_message
(
einvoice_message_id bigserial not null
constraint einvoice_message_pk
primary key,
message bytea not null,
created timestamp default now() not null
);

Answer 1

您的订单由轻微错误

ORDER BY :created 

排序依据需要列名，而不是值。你应该有

ORDER BY created