代码之家 › 专栏 › 技术社区 › m_pGladiator

如何修改PostgreSQL的SQL查询?

postgresql

m_pGladiator · 技术社区 · 16 年前

我有SQL查询,它在Oracle和MSSQL上工作得很好。现在我在PostgreSQL上尝试这个,它给出了一个奇怪的例外: org.postgresql.util.PSQLException:Â ERROR:Â missingÂ FROM-clauseÂ entryÂ forÂ tableÂ "main"

以下是查询:

SELECT *
FROM "main" main
     INNER JOIN "something_link" something_link ON main."id" = something_link."mainid"
     INNER JOIN "something" somehting ON something_link."somethingid" = something."id"
     INNER JOIN "type" type ON something."typeid" = type."id"

这是一个非常简单的查询,我不明白为什么它不能在Windows XP SP2、PostgreSQL 8.3上工作?

3 回复 | 直到 16 年前

Vinko Vrsalovic 16 年前

根据 this ,似乎您输入了错误的别名或使用了表名来代替它。

Vinko Vrsalovic 16 年前

somehting=>某物

postgres=# create database test
postgres-# ;
CREATE DATABASE

postgres=# \c test
You are now connected to database "test".

test=# select version();
                                            version                                            
-----------------------------------------------------------------------------------------------
 PostgreSQL 8.3.3 on i486-pc-linux-gnu, compiled by GCC cc (GCC) 4.2.3 (Ubuntu 4.2.3-2ubuntu7)

test=# create table main(id int);
CREATE TABLE

test=# create table something_link(mainid int);
CREATE TABLE

test=# create table something(id int);
CREATE TABLE

test=# create table type(id int);
CREATE TABLE

test=# alter table something add column typeid int;
ALTER TABLE

test=# SELECT *
test-# FROM "main" main
test-#      INNER JOIN "something_link" something_link ON main."id" = something_link."mainid"
test-#      INNER JOIN "something" somehting ON something_link."somethingid" = something."id"
test-#      INNER JOIN "type" type ON something."typeid" = type."id"
test-# ;
ERROR:  column something_link.somethingid does not exist
LINE 4:      INNER JOIN "something" somehting ON something_link."som...
                                                 ^
test=# alter table something_link add column somethingid int;
ALTER TABLE

test=# SELECT *                                              
FROM "main" main
     INNER JOIN "something_link" something_link ON main."id" = something_link."mainid"
     INNER JOIN "something" *somehting* ON something_link."somethingid" = something."id"
     INNER JOIN "type" type ON something."typeid" = type."id"
;

ERROR:  invalid reference to FROM-clause entry for table "something"
LINE 4: ...hing" somehting ON something_link."somethingid" = something....
                                                             ^
HINT:  Perhaps you meant to reference the table alias "somehting".

test=# SELECT *
FROM "main" main
     INNER JOIN "something_link" something_link ON main."id" = something_link."mainid"
     INNER JOIN "something" something ON something_link."somethingid" = something."id"
     INNER JOIN "type" type ON something."typeid" = type."id"
;

 id | mainid | somethingid | id | typeid | id 

----+--------+-------------+----+--------+----

(0 rows)

m_pGladiator 16 年前

真正的问题实际上不是查询,而是PostgreSQL 8.3的默认配置。在纠正拼写错误(10 x KendrickWilson)之后,问题一直存在,直到我编辑了“postgresql.conf”文件。应该有一行:

add_missing_from = on

这一行确保了与其他SQL方言的兼容性。