为什么POSTGRESQL的row_number()窗口函数不能在psycopg2中工作，而是在命令行或pgadmin中工作？

Question

我有以下查询来查找某个数据的行号。

cursor.execute("select row_number from (select row_number() over(order by date desc), * from contest where date <= %s order by date LIMIT (select count(*) from contest where date <= %s) - 1) as new WHERE date = %s", (today, today ,date))

执行此查询时，将收到以下错误消息：

psycopg2.ProgrammingError
psycopg2.ProgrammingError: syntax error at or near "select"
LINE 1: select row_number from (select row_number() over(order by d...

我在pgadmin4或postgresql命令行中执行相同的查询，如下所示。效果很好。

select row_number from (select row_number() over(order by date desc), * from contest where date <= '2017-5-5' order by date LIMIT (select count(*) from contest where date <='2017-5-5' ) - 1) as new WHERE date = '2017-2-1'

我使用postgresql和psycopg2的最新版本。

我被困在这里了。我找不到答案。我需要些帮助。

Answer 1

考虑到Tometzky的测试，这个问题很可能是一个不可打印的字符或类似的问题。

尝试打开log_error_verbosity = verbose并检查PostgreSQL错误日志以获得更多详细信息。

还检查您的源文件与不可打印字符显示打开。

否则..。从Psycopg2连接到同一个DB和相同的postgres版本与从psql连接到相同的postgres版本有多确定？

Answer 2

虽然我不知道您的查询试图做什么，但是当我从查询中创建SSCCE时，没有语法错误，而且它成功了：

import psycopg2, datetime

conn = psycopg2.connect("")
cursor = conn.cursor()

cursor.execute("create temporary table contest(id serial primary key, date date not null)")
cursor.execute("insert into contest (date) values (%s)", ("2000-01-01",))

today = datetime.date.today()
date = today-datetime.timedelta(days=1)
cursor.execute("""
    select row_number from (
        select row_number() over(order by date desc), *
        from contest
        where date <= %s
        order by date
        LIMIT ( select count(*) from contest where date <= %s) - 1
    ) as new
    WHERE date = %s""",
    (today, today, date)
)

使用Python-3.5/piccopg2-2.7.1和Python-2.7/piccopg2-2.6.2。问题似乎在其他地方。