PostgreSQL，Npgsql返回42601：在“ $ 1”或附近的语法错误。 [英] PostgreSQL, Npgsql returning 42601: syntax error at or near "$1"

问题描述

我正在尝试使用Npgsql和/或Dapper查询表，并且一直遇到 Npgsql.PostgresException 42601： $ 1或附近的语法错误。

I'm trying to use Npgsql and/or Dapper to query a table and I keep running into Npgsql.PostgresException 42601: syntax error at or near "$1".

这是我用NpgsqlCommand尝试的方法：

Here is what I've got trying it with NpgsqlCommand:

  using (var conn = new NpgsqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["postgres"].ConnectionString))
  {
    conn.Open();
    using (NpgsqlCommand command = new NpgsqlCommand("select * from Logs.Logs where Log_Date > current_date - interval @days day;", conn))
    {
      command.Parameters.AddWithValue("@days", days);
      var reader = command.ExecuteReader();

我也尝试过使用Dapper（我的首选方法）：

I've also tried it with Dapper(my preferred method) with:

  using (var conn = new NpgsqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["postgres"].ConnectionString))
  {
    conn.Open();
    var logs = conn.Query<Log>("select * from Logs.Logs where Log_Date > current_date - interval @days day;", new {days = days});

无论哪种方式，我都得到相同的 Npgsql.PostgresException 42601：或时语法错误异常中的语句显示：从Logs.Logs中选择*，其中Log_Date> current_date-间隔$ 1天

Either way I get the same Npgsql.PostgresException 42601: syntax error at or near "$1" error. The Statement in the Exception shows: select * from Logs.Logs where Log_Date > current_date - interval $1 day

请注意，如果执行以下操作，它可以正常工作，但未正确参数化：

Note, if I do the following it works fine, but it's not properly parameterized:

var logs = conn.Query<Log>("select * from Logs.Logs where Log_Date > current_date - interval '" + days + "' day;");

我在做什么错？我非常感谢您的任何反馈。谢谢。

What am I doing wrong? I very much appreciate any feedback. Thank you.

推荐答案

PostgreSQL不允许您在查询中的任何位置粘贴参数。可以通过以下操作实现您想要的：

PostgreSQL doesn't allow you to stick a parameter anywhere in a query. What you want can be achieved with the following:

var command = new NpgsqlCommand("select * from Logs.Logs where Log_Date > current_date - @days", conn))
command.Parameters.AddWithValue("@days", TimeSpan.FromDays(days));

这样，您可以直接将间隔从Npgsql传递到PostgreSQL，而不是表达式的一部分设计来创建该间隔。

This way you're passing the interval directly from Npgsql to PostgreSQL, rather than a part of the expression designed to create that interval.

这篇关于PostgreSQL，Npgsql返回42601：在“ $ 1”或附近的语法错误。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！