How should I modify a SQL query for PostgreSQL?

Question

I have SQL query, which is working nice on Oracle and MSSQL. Now I'm trying this on PostgreSQL and it gives a strange exception: org.postgresql.util.PSQLException: ERROR: missing FROM-clause entry for table "main"

Here is the query:

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"

This is quite simple query and I can't see why it is not working on Windows XP SP2, PostgreSQL 8.3?

Answer 1

somehting=>something

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)

Answer 2

According to this, seems like you either mistyped an alias or used a table name in place of it.

Answer 3

The real problem is actually not the query, but the PostgreSQL 8.3 default configuration. After correcting the spelling mistake (10x Kendrick Wilson), the problem persisted, until I edited the "postgresql.conf" file. There should be a line:

add_missing_from = on

This line ensures compatibility with the other SQL dialects.