pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedProblem with NOT IN portion of query.
3+ messages / 2 participants
[nested] [flat]
* Problem with NOT IN portion of query.
@ 2004-01-12 04:04 Robert Creager <Robert_Creager@LogicalChaos.org>
0 siblings, 1 reply; 3+ messages in thread
From: Robert Creager @ 2004-01-12 04:04 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Problem with NOT IN portion of query.
@ 2004-01-12 06:28 Tomasz Myrta <jasiek@klaster.net>
parent: Robert Creager <Robert_Creager@LogicalChaos.org>
0 siblings, 1 reply; 3+ messages in thread
From: Tomasz Myrta @ 2004-01-12 06:28 UTC (permalink / raw)
To: Robert Creager <Robert_Creager@LogicalChaos.org>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Problem with NOT IN portion of query.
@ 2004-01-12 13:05 Robert Creager <Robert_Creager@LogicalChaos.org>
parent: Tomasz Myrta <jasiek@klaster.net>
0 siblings, 0 replies; 3+ messages in thread
From: Robert Creager @ 2004-01-12 13:05 UTC (permalink / raw)
To: Tomasz Myrta <jasiek@klaster.net>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2004-01-12 13:05 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-01-12 04:04 Problem with NOT IN portion of query. Robert Creager <Robert_Creager@LogicalChaos.org>
2004-01-12 06:28 ` Tomasz Myrta <jasiek@klaster.net>
2004-01-12 13:05 ` Robert Creager <Robert_Creager@LogicalChaos.org>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox