pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedCOUNT(*) to find records which have a certain number of dependencies ?
16+ messages / 7 participants
[nested] [flat]
* COUNT(*) to find records which have a certain number of dependencies ?
@ 2004-09-20 15:14 T E Schmitz <mailreg@numerixtechnology.de>
0 siblings, 1 reply; 16+ messages in thread
From: T E Schmitz @ 2004-09-20 15:14 UTC (permalink / raw)
To: pgsql-sql
Hello,
I apologize in advance for this garbled message but I've been banging my
head against a brick-wall for a while and I just can't figure how to do
the following:
I have 3 tables BRAND,MODEL,TYPE which are related to each other:
BRAND
=====
BRAND_PK
BRAND_NAME
MODEL
=====
MODEL_PK
MODEL_NAME
BRAND_FK (NOT NULL, references BRAND_PK)
TYPE
====
TYPE_PK
TYPE_NAME
MODEL_FK (NOT NULL, references MODEL_PK)
------------------------------------
I want to select only those BRAND/MODEL combinations, where the MODEL
has more than one TYPE, but only where one of those has TYPE_NAME='xyz'.
I am not interested in MODELs with multiple TYPEs where none of them are
called 'xyz'.
--
Regards/Gruß,
Tarlika Elisabeth Schmitz
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: COUNT(*) to find records which have a certain number of
@ 2004-09-20 16:19 T E Schmitz <mailreg@numerixtechnology.de>
0 siblings, 3 replies; 16+ messages in thread
From: T E Schmitz @ 2004-09-20 16:19 UTC (permalink / raw)
To: pgsql-sql
I figured it eventually. (The only thing I don't know is where to put
the ORDER BY.)
> I want to select only those BRAND/MODEL combinations, where the MODEL
> has more than one TYPE, but only where one of those has TYPE_NAME='xyz'.
> I am not interested in MODELs with multiple TYPEs where none of them are
> called 'xyz'.
SELECT
BRAND_NAME,MODEL_NAME
FROM TYPE
left outer join MODEL on MODEL_PK =TYPE.MODEL_FK
left outer join BRAND on BRAND_PK =MODEL.BRAND_FK
group by BRAND.BRAND_NAME,MODEL_NAME
having count(*)>1
intersect
SELECT
BRAND_NAME,MODEL.MODEL_NAME
FROM TYPE
left outer join MODEL on MODEL_PK =TYPE.MODEL_FK
left outer join BRAND on BRAND_PK =MODEL.BRAND_FK
where TYPE_NAME='xyz'
group by BRAND.BRAND_NAME,MODEL_NAME
--
Regards/Gruß,
Tarlika Elisabeth Schmitz
--
Regards/Gruß,
Tarlika Elisabeth Schmitz
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: COUNT(*) to find records which have a certain number of
@ 2004-09-20 16:31 Rod Taylor <pg@rbt.ca>
parent: T E Schmitz <mailreg@numerixtechnology.de>
2 siblings, 1 reply; 16+ messages in thread
From: Rod Taylor @ 2004-09-20 16:31 UTC (permalink / raw)
To: mailreg@numerixtechnology.de; +Cc: pgsql-sql
On Mon, 2004-09-20 at 12:19, T E Schmitz wrote:
> I figured it eventually. (The only thing I don't know is where to put
> the ORDER BY.)
Try this:
SELECT brand_name, model_name
FROM (SELECT ... INTERSECT SELECT ...) AS t
ORDER BY ...
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: COUNT(*) to find records which have a certain number of
@ 2004-09-20 16:44 Martin Knipper <martin@mk-os.de>
parent: T E Schmitz <mailreg@numerixtechnology.de>
2 siblings, 1 reply; 16+ messages in thread
From: Martin Knipper @ 2004-09-20 16:44 UTC (permalink / raw)
To: pgsql-sql
Am 20.09.2004 18:19 schrieb T E Schmitz:
> I figured it eventually. (The only thing I don't know is where to put
> the ORDER BY.)
>
>
>>I want to select only those BRAND/MODEL combinations, where the MODEL
>>has more than one TYPE, but only where one of those has TYPE_NAME='xyz'.
>>I am not interested in MODELs with multiple TYPEs where none of them are
>>called 'xyz'.
>
>
Try this:
select b.brand_name,m.model_name,count(*) from model m inner join
brand b on m.brand_fk=b.brand_pk inner join type t on
m.model_pk=t.model_fk where t.type_name='xyz' group by
b.brand_name,m.model_name having count(m.model_name)>0;
Greetings,
Martin
--
Martin Knipper
www : http://www.mk-os.de
Mail : knipper@mk-os.de
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: COUNT(*) to find records which have a certain number of
@ 2004-09-20 17:59 T E Schmitz <mailreg@numerixtechnology.de>
parent: Rod Taylor <pg@rbt.ca>
0 siblings, 0 replies; 16+ messages in thread
From: T E Schmitz @ 2004-09-20 17:59 UTC (permalink / raw)
To: Rod Taylor <pg@rbt.ca>; +Cc: pgsql-sql
Hi Rod,
Rod Taylor wrote:
> On Mon, 2004-09-20 at 12:19, T E Schmitz wrote:
>
>>I figured it eventually. (The only thing I don't know is where to put
>>the ORDER BY.)
>
>
> Try this:
>
> SELECT brand_name, model_name
> FROM (SELECT ... INTERSECT SELECT ...) AS t
> ORDER BY ...
That does the trick. You're a genius ;-)
--
Regards/Gruß,
Tarlika
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: COUNT(*) to find records which have a certain number of dependencies ?
@ 2004-09-20 18:32 Greg Stark <gsstark@mit.edu>
parent: T E Schmitz <mailreg@numerixtechnology.de>
0 siblings, 1 reply; 16+ messages in thread
From: Greg Stark @ 2004-09-20 18:32 UTC (permalink / raw)
To: mailreg@numerixtechnology.de; +Cc: pgsql-sql
T E Schmitz <mailreg@numerixtechnology.de> writes:
> I want to select only those BRAND/MODEL combinations, where the MODEL has more
> than one TYPE, but only where one of those has TYPE_NAME='xyz'.
> I am not interested in MODELs with multiple TYPEs where none of them are called
> 'xyz'.
There are lots of approaches to this with various pros and cons.
The simplest one off the top of my head:
select *
from brand
join model on (brand_pk = brand_fk)
where exists (select 1 from type where model_fk = model_pk and type_name = 'xyz')
and (select count(*) from type where model_fk = model_pk) > 1
You could try to be clever about avoiding the redundant access to the type table:
select *
from brand
join model on (brand_pk = brand_fk)
where (select count(*)
from type
where model_fk = model_pk
having sum(case when type = 'xyz' then 1 else 0 end) >= 1
) > 1
I'm haven't tested that, it might need some tweaking. In any case I don't
think it's worth the added complexity, assuming you have indexes on type. I'm
not even sure it would run faster.
You could try to be really clever about it by turning the whole thing into a
join:
select *
from brand
join model on (brand_pk = brand_fk)
join (select model_fk
from type
group by model_fk
having sum(case when type = 'xyz' then 1 else 0 end) >= 1
and count(*) > 1
) on (model_fk = model_pk)
This would let the planner have a more plans to choose from and might be a big
win if there are lots of brands and models but few that satisfy the criteria
you're looking for.
--
greg
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: COUNT(*) to find records which have a certain number of
@ 2004-09-20 18:33 Greg Stark <gsstark@mit.edu>
parent: T E Schmitz <mailreg@numerixtechnology.de>
2 siblings, 0 replies; 16+ messages in thread
From: Greg Stark @ 2004-09-20 18:33 UTC (permalink / raw)
To: mailreg@numerixtechnology.de; +Cc: pgsql-sql
T E Schmitz <mailreg@numerixtechnology.de> writes:
> SELECT
> BRAND_NAME,MODEL_NAME
...
> intersect
...
Huh, I never think of the set operation solutions. I'm curious how it compares
speed-wise.
--
greg
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: COUNT(*) to find records which have a certain number of
@ 2004-09-20 19:50 T E Schmitz <mailreg@numerixtechnology.de>
parent: Greg Stark <gsstark@mit.edu>
0 siblings, 2 replies; 16+ messages in thread
From: T E Schmitz @ 2004-09-20 19:50 UTC (permalink / raw)
To: Greg Stark <gsstark@mit.edu>; pgsql-sql
Hello Greg,
You have given me plenty of food for thought. Thank you for taking the
time.
Currently, the tables have such few records (350, 900, 1000) that
performance does not come into it, particularly seeing as this was only
needed for a one-shot report.
However, I have stached your examples away for future reference.
I was feeling a bit guilty about posting such a trivial question. I can
cobble together some straightforward SQL but I could really do with a
source of more complex SQL examples.
If you know of any links - that would great and save the list from more
such questions ;-)
I am correcting a couple of typos below in case someone tries these
examples out.
Greg Stark wrote:
> select *
> from brand
> join model on (brand_pk = brand_fk)
> where (select count(*)
> from type
> where model_fk = model_pk
> having sum(case when type = 'xyz' then 1 else 0 end) >= 1
> ) > 1
... having sum(case when type_name = 'xyz' ...
> select *
> from brand
> join model on (brand_pk = brand_fk)
> join (select model_fk
> from type
> group by model_fk
> having sum(case when type = 'xyz' then 1 else 0 end) >= 1
> and count(*) > 1
> ) on (model_fk = model_pk)
>
) as somealias on (model_fk = model_pk)
(subquery in FROM must have an alias)
--
Regards/Gruß,
Tarlika
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: COUNT(*) to find records which have a certain number of dependencies ?
@ 2004-09-20 20:45 Greg Stark <gsstark@mit.edu>
parent: T E Schmitz <mailreg@numerixtechnology.de>
1 sibling, 2 replies; 16+ messages in thread
From: Greg Stark @ 2004-09-20 20:45 UTC (permalink / raw)
To: mailreg@numerixtechnology.de; +Cc: Greg Stark <gsstark@mit.edu>; pgsql-sql
T E Schmitz <mailreg@numerixtechnology.de> writes:
> ) as somealias on (model_fk = model_pk)
>
> (subquery in FROM must have an alias)
ARGH! This is one of the most annoying things about postgres! It bites me all
the time. Obviously it's totally insignificant since it's easy for my to just
throw an "AS x" on the end of it. But damn.
I see there's a comment foreseeing some annoyance value for this in the
source:
/*
* The SQL spec does not permit a subselect
* (<derived_table>) without an alias clause,
* so we don't either. This avoids the problem
* of needing to invent a unique refname for it.
* That could be surmounted if there's sufficient
* popular demand, but for now let's just implement
* the spec and see if anyone complains.
* However, it does seem like a good idea to emit
* an error message that's better than "syntax error".
*/
So where can I officially register my complaint? :)
--
greg
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: COUNT(*) to find records which have a certain number of
@ 2004-09-20 20:55 Chester Kustarz <chester@arbor.net>
parent: T E Schmitz <mailreg@numerixtechnology.de>
1 sibling, 0 replies; 16+ messages in thread
From: Chester Kustarz @ 2004-09-20 20:55 UTC (permalink / raw)
To: T E Schmitz <mailreg@numerixtechnology.de>; +Cc: pgsql-sql
On Mon, 20 Sep 2004, T E Schmitz wrote:
> I was feeling a bit guilty about posting such a trivial question. I can
> cobble together some straightforward SQL but I could really do with a
> source of more complex SQL examples.
> If you know of any links - that would great and save the list from more
> such questions ;-)
SQL for Smarties has some more complicated examples and topics for
"advanced" type queries. I can't say it's exhaustive, but I found it
a good bridge by hinting at what is really possible. I also found that
a good way to improve is to try to write every complicated query by
using all the different ways I can think of, like:
- UNION (ALL)
- SUB-SELECT
- LEFT OUTER JOINS
- HAVING
etc.
Here is the link for SQL for Smarties:
http://www.amazon.com/exec/obidos/tg/detail/-/1558605762/002-2222957-7220055?v=glance
The bad thing about the book is that it is sort of SQL agnostic, so
some of the examples would be sub-optimal on postgresql, or may not
even work.
I would like to hear about other sources too.
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: [SQL] COUNT(*) to find records which have a certain number of dependencies ?
@ 2004-09-20 21:39 Greg Stark <gsstark@mit.edu>
parent: Greg Stark <gsstark@mit.edu>
1 sibling, 1 reply; 16+ messages in thread
From: Greg Stark @ 2004-09-20 21:39 UTC (permalink / raw)
To: mailreg@numerixtechnology.de; pgsql-patches@postgresql.org; +Cc: Greg Stark <gsstark@mit.edu>
T E Schmitz <mailreg@numerixtechnology.de> writes:
> There's a German saying "Go and find a parking-meter", i.e. suggesting to pop a
> coin in the parking-meter and talk to it as nobody else wants to listen. ;-)
Yes well I anticipated such a response. So I tried my hand at it myself.
Well I finally found a problem tractable enough for me to get all the way from
start to end in a single sitting. Here's a simple solution to my complaint.
This patch allows subqueries without aliases. This is SQL-non-spec-compliant
syntax that Oracle supports and many users expect to work. It's also just
damned convenient, especially for simple ad-hoc queries.
There was a comment saying an alias name would have to be constructed so I
took that approach. It seems like it would have been cleaner to just ensure
that the code doesn't fail when no alias is present. But I have no idea how
much work would be involved in that, so I just took advice from the anonymous
author of the comment.
Incidentally, It seems weird to me that the counter doesn't reset for every
query. Perhaps I should change that?
Index: src/backend/parser/gram.y
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/backend/parser/gram.y,v
retrieving revision 2.475
diff -u -p -c -r2.475 gram.y
cvs diff: conflicting specifications of output style
inline patch 1: src/backend/parser/gram.y, src/backend/parser/parse_clause.c (+8/-13 lines) download inline-1.patch
*** src/backend/parser/gram.y 29 Aug 2004 04:12:35 -0000 2.475
--- src/backend/parser/gram.y 20 Sep 2004 21:34:13 -0000
*************** table_ref: relation_expr
*** 5158,5177 ****
{
/*
* The SQL spec does not permit a subselect
! * (<derived_table>) without an alias clause,
! * so we don't either. This avoids the problem
! * of needing to invent a unique refname for it.
! * That could be surmounted if there's sufficient
! * popular demand, but for now let's just implement
! * the spec and see if anyone complains.
! * However, it does seem like a good idea to emit
! * an error message that's better than "syntax error".
*/
! ereport(ERROR,
! (errcode(ERRCODE_SYNTAX_ERROR),
! errmsg("subquery in FROM must have an alias"),
! errhint("For example, FROM (SELECT ...) [AS] foo.")));
! $$ = NULL;
}
| select_with_parens alias_clause
{
--- 5158,5172 ----
{
/*
* The SQL spec does not permit a subselect
! * (<derived_table>) without an alias clause, We surmount
! * this because of popular demand by gining up a fake name
! * in transformRangeSubselect
*/
!
! RangeSubselect *n = makeNode(RangeSubselect);
! n->subquery = $1;
! n->alias = NULL;
! $$ = (Node *) n;
}
| select_with_parens alias_clause
{
Index: src/backend/parser/parse_clause.c
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/backend/parser/parse_clause.c,v
retrieving revision 1.136
diff -u -p -c -r1.136 parse_clause.c
inline patch 2: src/backend/parser/parse_clause.c (+11/-3 lines) download inline-2.patch
*** src/backend/parser/parse_clause.c 29 Aug 2004 05:06:44 -0000 1.136
--- src/backend/parser/parse_clause.c 20 Sep 2004 21:34:14 -0000
*************** transformRangeSubselect(ParseState *psta
*** 418,426 ****
* an unlabeled subselect.
*/
if (r->alias == NULL)
! ereport(ERROR,
! (errcode(ERRCODE_SYNTAX_ERROR),
! errmsg("subquery in FROM must have an alias")));
/*
* Analyze and transform the subquery.
--- 418,434 ----
* an unlabeled subselect.
*/
if (r->alias == NULL)
! {
! static int subquery_counter = 1;
! static char buf[30];
!
! sprintf(buf, "*SUBQUERY*%d*", subquery_counter++);
!
! r->alias = makeNode(Alias);
! r->alias->aliasname = pstrdup(buf);
! r->alias->colnames = NULL;
! }
!
/*
* Analyze and transform the subquery.
* Re: [SQL] COUNT(*) to find records which have a certain number of dependencies ?
@ 2004-09-20 23:23 Tom Lane <tgl@sss.pgh.pa.us>
parent: Greg Stark <gsstark@mit.edu>
0 siblings, 1 reply; 16+ messages in thread
From: Tom Lane @ 2004-09-20 23:23 UTC (permalink / raw)
To: Greg Stark <gsstark@mit.edu>; +Cc: mailreg@numerixtechnology.de; pgsql-patches@postgresql.org
Greg Stark <gsstark@mit.edu> writes:
> This patch allows subqueries without aliases. This is SQL-non-spec-compliant
> syntax that Oracle supports and many users expect to work.
AFAIR you're the first to propose that we ignore the SQL spec here.
When I wrote "see if anyone complains", I had in mind waiting for
quite a few complaints before contemplating changing this...
regards, tom lane
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: [SQL] COUNT(*) to find records which have a certain number of dependencies ?
@ 2004-09-21 17:14 Greg Stark <gsstark@mit.edu>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 16+ messages in thread
From: Greg Stark @ 2004-09-21 17:14 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Greg Stark <gsstark@mit.edu>; mailreg@numerixtechnology.de; pgsql-patches@postgresql.org
Tom Lane <tgl@sss.pgh.pa.us> writes:
> Greg Stark <gsstark@mit.edu> writes:
> > This patch allows subqueries without aliases. This is SQL-non-spec-compliant
> > syntax that Oracle supports and many users expect to work.
>
> AFAIR you're the first to propose that we ignore the SQL spec here.
> When I wrote "see if anyone complains", I had in mind waiting for
> quite a few complaints before contemplating changing this...
I understand. But I expect there are lots of users this annoys. It's just such
an easy thing to work around that it would be a petty thing to complain about.
That's the only reason I never complained about it all this time it was
annoying me. Not until I felt ready to poke around and fix it myself.
And I'm not suggesting ignoring the spec, just allowing the user to ignore it,
and not having postgres go out of its way to enforce the spec on users.
I doubt the spec says that the implementation cannot allow the syntax.
...Ok, well I wouldn't put it past the spec to do so. But it does so about
lots of things that Postgres allows. The general attitude postgres has seems
to be to extend the spec whenever it's nice for the user as long as doing so
doesn't interfere with actually supporting any spec compliant code. It's not
like postgres is a good platform for testing spec compliance of SQL code
otherwise.
It just seems excessively obnoxious to refuse queries because they don't match
a grammar precisely when the missing piece is entirely not needed by the
database. It doesn't cause any ambiguity or other problems with the SQL. It
doesn't cost a single cycle in the normal case. The code doesn't impact
anything else in the system. It's the database being intentionally nosy and
picky about something just because it can.
--
greg
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: COUNT(*) to find records which have a certain number of dependencies
@ 2004-09-21 22:16 Mischa Sandberg <ischamay.andbergsay@activestateway.com>
parent: Greg Stark <gsstark@mit.edu>
1 sibling, 1 reply; 16+ messages in thread
From: Mischa Sandberg @ 2004-09-21 22:16 UTC (permalink / raw)
To: pgsql-sql
Greg Stark wrote:
> T E Schmitz <mailreg@numerixtechnology.de> writes:
>>) as somealias on (model_fk = model_pk)
>>(subquery in FROM must have an alias)
>
> ARGH! This is one of the most annoying things about postgres! It bites me all
> the time. Obviously it's totally insignificant since it's easy for my to just
> throw an "AS x" on the end of it. But damn.
>
> So where can I officially register my complaint? :)
Hope you don't mind an opinion from someone who looks at this from the
underside ...
Trying to give the parser a better chance of confusing you?
Having the tag only totally insignificant if you want to have a bunch of
special validation cases, where if there is only ONE anonymous
pseudotable, and no ambiguity is possible.
If all it does is give you an annoying but understandable error message,
might you care to consider the cryptic error messages you get from
systems that try to 'do what you mean, not what you say' for such
special cases ... and thereby turn a typo into an error twenty lines
further down the page.
BTW, the "as" is optional, but I always suggest that people use it
explicitly. Why? Because without it, you get another silly error message
or even a runtime error when what you did was omit a comma. For example
select salary name from Employee
returns one column (a dollar figure called "name").
(Yes, I know it's harder to cook up an example when the comma is missing
between tables in the FROM list; just wanted it to be obvious)
Okay, apologies for what may sound like a rant.
I've just been wrangling with an interpreter that tries WAY too hard to
make something executable out of what you tell it ... even if that's
really nothing like your intent.
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: COUNT(*) to find records which have a certain number of
@ 2004-09-27 09:45 T E Schmitz <mailreg@numerixtechnology.de>
parent: Martin Knipper <martin@mk-os.de>
0 siblings, 0 replies; 16+ messages in thread
From: T E Schmitz @ 2004-09-27 09:45 UTC (permalink / raw)
To: Martin Knipper <martin@mk-os.de>; pgsql-sql
Hallo Martin,
Martin Knipper wrote:
> Am 20.09.2004 18:19 schrieb T E Schmitz:
>
>>>I want to select only those BRAND/MODEL combinations, where the MODEL
>>>has more than one TYPE, but only where one of those has TYPE_NAME='xyz'.
>>>I am not interested in MODELs with multiple TYPEs where none of them are
>>>called 'xyz'.
>>
>>
>
> Try this:
>
> select b.brand_name,m.model_name,count(*) from model m inner join
> brand b on m.brand_fk=b.brand_pk inner join type t on
> m.model_pk=t.model_fk where t.type_name='xyz' group by
> b.brand_name,m.model_name having count(m.model_name)>0;
>
Sorry, no, this doesn't work. It definitely doesn't return the required
resultset. It just returns all BRAND_NAME,MODEL_NAME for all Models that
have a Type with TYPE_NAME='xyz'.
--
Regards/Gruß,
Tarlika Elisabeth Schmitz
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: COUNT(*) to find records which have a certain number of dependencies
@ 2004-09-27 13:27 Greg Stark <gsstark@mit.edu>
parent: Mischa Sandberg <ischamay.andbergsay@activestateway.com>
0 siblings, 0 replies; 16+ messages in thread
From: Greg Stark @ 2004-09-27 13:27 UTC (permalink / raw)
To: ischamay.andbergsay@activestateway.com; +Cc: pgsql-sql
Mischa Sandberg <ischamay.andbergsay@activestateway.com> writes:
> Hope you don't mind an opinion from someone who looks at this from the
> underside ...
>
> Trying to give the parser a better chance of confusing you?
Ok, I understand the basic idea that a parser muddles along too long before
reporting an error makes it harder to track down the original error. However
this isn't such a case. I can't think of any way I could accidentally
introduce an extra comma that would lead to a valid looking alias and an error
much further along. You're talking about something like:
select * from a, as x
which would still produce a syntax error directly after the erroneous comma.
Which is right where you want the error to happen.
> Having the tag only totally insignificant if you want to have a bunch of
> special validation cases, where if there is only ONE anonymous pseudotable,
> and no ambiguity is possible.
There's no additional ambiguity. There can't be since all the user would do to
make the parser happy is go and specify aliases, not necessarily use them. If
the user didn't feel the need to put aliases in initially presumably it was
because he wasn't using them because he didn't need them.
Can you really say queries like this are dangerous:
select a_id,b_id
from (select a_id from a where xyz=?),
(select b_id from b where xyz=?)
or queries like
select a_id,b_id
from (select a_id from a where xyz=?) as x,
(select b_id from b where xyz=?) as x
are any clearer or less ambiguous?
> If all it does is give you an annoying but understandable error message
The reason it's annoying is because the database is saying, "I know perfectly
well what you're doing: I parsed the subquery fine, but I'm going to refuse to
run it because you didn't say the magic word." But when I add in "as x" after
every subquery even though it's utterly meaningless to the rest of the query,
then postgres is perfectly happy to cooperate.
> BTW, the "as" is optional, but I always suggest that people use it explicitly.
> Why? Because without it, you get another silly error message or even a runtime
> error when what you did was omit a comma. For example
>
> select salary name from Employee
I understand what you mean but I don't understand how always using the "as"
helps you here. You'll still get this error even if you were always using AS
when you intended. There's no option to make AS mandatory.
Incidentally, I also always use AS in both column and table aliases (when
specifying them) but for a different reason. I could never keep straight which
of Oracle and MSSQL required AS for columns and which required it for tables.
IIRC they're exactly reversed. However as best as I recall they both allowed
subqueries without any aliases at all.
--
greg
^ permalink raw reply [nested|flat] 16+ messages in thread
end of thread, other threads:[~2004-09-27 13:27 UTC | newest]
Thread overview: 16+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-09-20 15:14 COUNT(*) to find records which have a certain number of dependencies ? T E Schmitz <mailreg@numerixtechnology.de>
2004-09-20 18:32 ` Greg Stark <gsstark@mit.edu>
2004-09-20 19:50 ` Re: COUNT(*) to find records which have a certain number of T E Schmitz <mailreg@numerixtechnology.de>
2004-09-20 20:45 ` Greg Stark <gsstark@mit.edu>
2004-09-20 21:39 ` Greg Stark <gsstark@mit.edu>
2004-09-20 23:23 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-09-21 17:14 ` Greg Stark <gsstark@mit.edu>
2004-09-21 22:16 ` Re: COUNT(*) to find records which have a certain number of dependencies Mischa Sandberg <ischamay.andbergsay@activestateway.com>
2004-09-27 13:27 ` Re: COUNT(*) to find records which have a certain number of dependencies Greg Stark <gsstark@mit.edu>
2004-09-20 20:55 ` Re: COUNT(*) to find records which have a certain number of Chester Kustarz <chester@arbor.net>
2004-09-20 16:19 Re: COUNT(*) to find records which have a certain number of T E Schmitz <mailreg@numerixtechnology.de>
2004-09-20 16:31 ` Re: COUNT(*) to find records which have a certain number of Rod Taylor <pg@rbt.ca>
2004-09-20 17:59 ` Re: COUNT(*) to find records which have a certain number of T E Schmitz <mailreg@numerixtechnology.de>
2004-09-20 16:44 ` Re: COUNT(*) to find records which have a certain number of Martin Knipper <martin@mk-os.de>
2004-09-27 09:45 ` Re: COUNT(*) to find records which have a certain number of T E Schmitz <mailreg@numerixtechnology.de>
2004-09-20 18:33 ` Re: COUNT(*) to find records which have a certain number of Greg Stark <gsstark@mit.edu>
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