Django 创建的 SELECT DISTINCT、ORDER BY 适用于 MySQL 和 SQLite，不适用于 PostgreSQL

发布于 2024-10-14 20:10:12 字数 445 浏览 2 评论 0原文

我刚刚从 mysql 切换到 postgres （耶！），但现在在我的 postgres 服务器上（仅）我收到一个错误：

SELECT DISTINCT, ORDER BY 表达式必须出现在选择列表中

这是相同的查询：

SELECT DISTINCT * FROM table ORDER BY '?'

顺便说一句，这是一个查询自动-由 django 生成。或者，有什么方法可以让 django 真正渲染正确的 sql？

附录事实

证明，无论出于何种原因，在我的 Postgres 8.4 生产服务器上，Django 消除了错误，而在我的 Postgres 9.0 开发服务器上则不然。我不知道它是否与 Postgres 版本有关，但实际上在 Postgreses 中都出错，而不是在 MySQL 中。

原文

I just switched from mysql to postgres (yay!) but now on my postgres server (only) I'm getting an error that:

SELECT DISTINCT, ORDER BY expressions must appear in select list

It's the same query:

SELECT DISTINCT * FROM table ORDER BY '?'

BTW this is a query auto-generated by django. Alternatively, any way to get django to actually render correct sql?

Addendum

It turns out that for whatever reason, with my Postgres 8.4 production server, Django was quashing the error, while with my Postgres 9.0 dev server it was not. I have no idea if it was related to the Postgres versions or not, but it WAS in fact erroring in both Postgreses and NOT in MySQL.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

原来是傀儡 2024-10-21 20:10:12

我预计所有符合 SQL 标准的 SQL 平台都会引发类似的错误。

这样想吧。

CREATE TABLE test (
    id INTEGER NOT NULL,
    c CHAR(1) NOT NULL,
    PRIMARY KEY (id, c)
);

INSERT INTO test VALUES 
    (1, 'a'),
    (1, 'z'),
    (2, 'b');

SELECT * FROM test ORDER BY c;

id  c
--  -
1   a
2   b
1   z

现在想想下一句话的含义。

SELECT DISTINCT id FROM test ORDER BY c;

dbms 应使用哪个值“c”来在排序顺序中定位“id”1？如果选择“a”，则顺序为“1,2”。如果选择“z”，则顺序为“2,1”。

I'd expect all SQL platforms that comply with SQL standards to raise a similar error.

Think about it this way.

CREATE TABLE test (
    id INTEGER NOT NULL,
    c CHAR(1) NOT NULL,
    PRIMARY KEY (id, c)
);

INSERT INTO test VALUES 
    (1, 'a'),
    (1, 'z'),
    (2, 'b');

SELECT * FROM test ORDER BY c;

id  c
--  -
1   a
2   b
1   z

Now think about what this next statement means.

SELECT DISTINCT id FROM test ORDER BY c;

Which value of 'c' should the dbms use to position 'id' 1 in the sort order? If it chooses 'a', the order is '1,2'. If it chooses 'z', the order is '2,1'.

回复收藏 0 原文