Thread: Problem with NOT IN portion of query.

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.

Any help?

Cheers,
Rob

-- 20:55:35 up 14 days, 10:45,  4 users,  load average: 2.01, 2.04, 2.05

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

When grilled further on (Mon, 12 Jan 2004 07:28:09 +0100),
Tomasz Myrta <jasiek@klaster.net> confessed:

> Dnia 2004-01-12 05:04, U¿ytkownik Robert Creager napisa³:
> >
> > 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;
> >
> > The error is:
> >
> > ERROR:  column "period" does not exist
> >
> 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".

Thanks for the info.

So now I have:

SELECT p.period FROM (SELECT date_trunc( 'hour', "when" )::timestamp AS period
FROM readings GROUP BY period) AS p WHERE p.period NOT IN (SELECT "time" FROM
hour.summary_period) ORDER BY p.period;

which appears works as expected.  Anything obviously goofy with the above query?

Cheers,
Rob
-- 05:57:10 up 14 days, 19:47,  4 users,  load average: 2.17, 2.07, 2.04