Re: [SQL] Problem with NOT IN portion of query.

[Tomasz Myrta]

Dnia 2004-01-12 05:04, Użytkownik Robert Creager napisał:
Hey All,

Probably doing something stupid, and I'm too tired to see what.  The query I'm
trying to execute is:
SELECT date_trunc( 'hour', "when" )::timestamp AS
period FROM readings WHERE period NOT IN (SELECT "time" FROM
hour.summary_period) GROUP BY period ORDER BY period;
Where the table definitions are:

CREATE TABLE readings ( "when" TIMESTAMP DEFAULT now() NOT NULL PRIMARY KEY );
CREATE SCHEMA hour;
CREATE TABLE hour.summary_period ( "time" TIMESTAMP NOT NULL );
The error is:

ERROR:  column "period" does not exist

When I remove the NOT IN (and associated WHERE), the query works fine.
Your problem has nothing to "NOT IN".
Your query works fine, when you remove column alias from WHERE clause - 
it's beacause WHERE clause is executed *before* data output (and before 
column aliases). You can still use column aliases in "GROUP BY" and 
"ORDER BY".

Regards,
Tomasz Myrta
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster