Re: [SQL] problem with select where like ']'
Tom Lane wrote: > > hubert depesz lubaczewski <[EMAIL PROTECTED]> writes: > > but i'm just wondering why like ']' doesn't work. > > What LOCALE setting are you running the postmaster in? > > ']' is not a special character as far as LIKE is concerned, but > I suspect you may be seeing another variant of the problems that > LIKE index optimization has with peculiar collation rules. > You can find plenty of discussion of this in the mailing list archives > :-( > WHAT mailing list archives? They aren't linked to anywhere on www.postgresql.org that I can find. -- Joseph Shraibman [EMAIL PROTECTED] Increase signal to noise ratio. http://www.targabot.com
Re: [SQL] problem with select where like ']'
The only way I can find is to do a search on something, and select to search in mailing lists. Then after the search returns click on a link, and trucate the url to http://www.postgresql.org/mhonarc/ Bruce Momjian wrote: > > The fact is, I can't figure out how to get there without the URL. > > > Joseph Shraibman <[EMAIL PROTECTED]> writes: > > > WHAT mailing list archives? > > > They aren't linked to anywhere on www.postgresql.org that I can find. > > > > Hmm. My bookmark is > > > > http://www.postgresql.org/lists/mailing-list.html > > > > Dunno how to get there from the site toplevel... > > > > regards, tom lane > > > > -- > Bruce Momjian| http://candle.pha.pa.us > [EMAIL PROTECTED] | (610) 853-3000 > + If your life is a hard drive, | 830 Blythe Avenue > + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 -- Joseph Shraibman [EMAIL PROTECTED] Increase signal to noise ratio. http://www.targabot.com
[SQL] subselects
I tried to do this: SELECT r , a , (SELECT u , re FROM dir WHERE u = a) , cdate FROM rep WHERE m IN(190); ... and I got: ERROR: Subselect must have only one field An explain shows that two subselects result in two queries, even thought they are accessing the same row. Why can subselects only have one field? Database version: PostgreSQL 7.0.3 on i686-pc-linux-gnu, compiled by gcc egcs-2.91.66 -- Joseph Shraibman [EMAIL PROTECTED] Increase signal to noise ratio. http://www.targabot.com
[SQL] alter table question
How do I alter a table to set a column to be not null? -- Joseph Shraibman [EMAIL PROTECTED] Increase signal to noise ratio. http://www.targabot.com
Re: [SQL] subselects
hubert depesz lubaczewski wrote: > > On Wed, Nov 29, 2000 at 07:03:36PM -0500, Joseph Shraibman wrote: > > I tried to do this: > > SELECT r , a , (SELECT u , re FROM dir WHERE u = a) , cdate FROM rep > > WHERE m IN(190); > > why dont you use simple join? > like: > select rep.r, rep.a, rep.cdate, dir.u, dir.re from rep, dir where rep.m in > (190) and rep.a = dir.u; > > this should (i guess) work perfectly > Because in my case I'm dynamically generated the select by passing in some String values, which are inserted into SELECT x,x,x from mytable so I can't do a simple join. -- Joseph Shraibman [EMAIL PROTECTED] Increase signal to noise ratio. http://www.targabot.com
[SQL] FOREIGN KEY errors.
When trying to alter a table and add a foreign key, I am getting this error if the table has any data in it: playpen=# alter table message add FOREIGN KEY (pod,originator) REFERENCES usertable (podkey,userkey); NOTICE: ALTER TABLE ... ADD CONSTRAINT will create implicit trigger(s) for FOREIGN KEY check(s) ERROR: constraint : table usertable does not have an attribute originator If I do the alter before I put any data in the table: playpen=# alter table message add FOREIGN KEY (pod,originator) REFERENCES usertable (podkey,userkey); NOTICE: ALTER TABLE ... ADD CONSTRAINT will create implicit trigger(s) for FOREIGN KEY check(s) CREATE playpen=# select version(); version - PostgreSQL 7.0.3 on i686-pc-linux-gnu, compiled by gcc egcs-2.91.66 (1 row) Reversing the order creates a different message: playpen=# alter table message add FOREIGN KEY (originator,pod) REFERENCES usertable (userkey,podkey); NOTICE: ALTER TABLE ... ADD CONSTRAINT will create implicit trigger(s) for FOREIGN KEY check(s) ERROR: constraint : table usertable does not have an attribute pod Am I just misunderstanding how to use FOREIGN KEY? Then why would it work one time and not the other? http://www.postgresql.org/docs/aw_pgsql_book/node159.html doesn't have any column names after 'refrences '. -- Joseph Shraibman [EMAIL PROTECTED] Increase signal to noise ratio. http://www.targabot.com
Re: [SQL] FOREIGN KEY errors.
Script to reproduce the problem. It drops the tables at the end of the
script.
Stephan Szabo wrote:
>
> There was a bug (which should be fixed for 7.1) that got the
> arguments wrong for the alter time check of the existing data.
> I think I should be able to get a patch together to fix it once
> I get a copy of the 7.0.3 source.
>
> Can you send the table schema as well so I can test it out?
>
Script to reproduce the problem. It drops the tables at the end of the
script. If data is not inserted into mtable there is no problem.
create table utable (
uk int not null,
pk int not null,
thetext text,
primary key (uk, pk)
);
create table mtable(
id serial not null primary key,
mtext text,
o int, /* corresponds to uk */
p int /* corresponds to pk */
);
insert into utable (uk,pk,thetext) values (2,4,'blah 2 4');
insert into utable (uk,pk,thetext) values (2,5,'blah 2 5');
insert into mtable (mtext,o,p) values ('m 2 4',2,4);
alter table mtable add FOREIGN KEY (p,o) REFERENCES utable (pk,uk);
drop sequence mtable_id_seq;
drop table mtable;
drop table utable;
--
Joseph Shraibman
[EMAIL PROTECTED]
Increase signal to noise ratio. http://www.targabot.com
Re: [SQL]
No. > Peeter Smitt wrote: > > Hi > > Is it somehow possible to join tables from multiple databases into one > query? > > Thanks > > Peeter > > -- Joseph Shraibman [EMAIL PROTECTED] Increase signal to noise ratio. http://www.targabot.com
Re: [SQL] postgres
"Brett W. McCoy" wrote: > > On 13 Dec 2000, Marc Daoust wrote: > > > I in the search for a DB that would work with our product and have been told > > to have a look at postgres. Would you be able to foward me any information on > > your product and or point me to where I might be able to find some. > > You should start with www.postgresql.org > I've been wondering for a long time how people manage to find the mailing list without finding the web site. On the blackdown mailing list (blackdown.org ports the jdk to linux) people ask where they can get a jdk for linux. How did they find the list without knowing about blackdown? -- Joseph Shraibman [EMAIL PROTECTED] Increase signal to noise ratio. http://www.targabot.com
Re: [SQL] PostgreSQL HOWTO
Alvar Freude wrote: > > Hi, > > Poet/Joshua Drake wrote: > > > > >it seems that the author never used any other think then PHP ... > > > > I am afraid I would disagree. I have used all of the languages he metions > > and for the Web, PHP is the best. > > hmm, i guess it's not a good place for a PHP/Perl war here ;) but: > The author wrote that PHP is Perl, C, Java etc. all in one -- this seems > to me that he doesn't know perl. > He also wrote (http://www.linux.org/docs/ldp/howto/PostgreSQL-HOWTO-2.html): "Nuclear weapons and other more powerful divine weapons were used in the battle field in ancient India!" I'm mystified as to why this document is on the linux.org web site. -- Joseph Shraibman [EMAIL PROTECTED] Increase signal to noise ratio. http://www.targabot.com
[SQL] how to do plpgsql?
When trying to do some of the examples on http://www.postgresql.org/docs/postgres/c40914344.htm I keep getting: ERROR: Unrecognized language specified in a CREATE FUNCTION: 'plpgsql'. Recognized languages are sql, C, internal and the created procedural languages. version is: PostgreSQL 7.0.3 on i686-pc-linux-gnu, compiled by gcc egcs-2.91.66 -- Joseph Shraibman [EMAIL PROTECTED] Increase signal to noise ratio. http://www.targabot.com
Re: [SQL] how to do plpgsql?
Huh. You'd think this would be prominent in the documentation page at http://www.postgresql.org/docs/postgres/c4091.htm Thanks. Josh Berkus wrote: > > Joseph, > > First you need to install plpgsql on a per database > basis, or you can just install it on template1 and it > will get added to all new databases. > > CREATE FUNCTION "plpgsql_call_handler" ( ) RETURNS opaque AS > '/usr/lib/pgsql/plpgsql.so' LANGUAGE 'C'; > > CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql' HANDLER > "plpgsql_call_handler" LANCOMPILER 'PL/pgSQL'; > > -Josh Berkus > > (Instructions courtesy of Jeff at PGSQL Inc.) > -- > __AGLIO DATABASE SOLUTIONS___ > Josh Berkus >Complete information technology [EMAIL PROTECTED] > and data management solutions (415) 565-7293 > for law firms, small businesses fax 621-2533 > and non-profit organizations. San Francisco -- Joseph Shraibman [EMAIL PROTECTED] Increase signal to noise ratio. http://www.targabot.com
[SQL] plpgsql error: cache lookup from pg_proc failed
playpen=# create table aa( playpen(# a int, playpen(# b int, playpen(# t timestamp playpen(# ); CREATE playpen=# playpen=# playpen=# CREATE FUNCTION touch () RETURNS OPAQUE AS ' playpen'# BEGIN playpen'# new.t := current_timestamp; playpen'# RETURN new; playpen'# END; playpen'# ' LANGUAGE 'plpgsql'; CREATE playpen=# playpen=# playpen=# CREATE TRIGGER lastmod_1 AFTER update or insert ON aa FOR EACH ROW EXECUTE PROCEDURE touch(); CREATE playpen=# insert into aa (a, b) values (1,2); ERROR: plpgsql: cache lookup from pg_proc failed What does this error message mean? -- Joseph Shraibman [EMAIL PROTECTED] Increase signal to noise ratio. http://www.targabot.com
Re: [SQL] RE: plpgsql error: cache lookup from pg_proc failed
Michael Davis wrote: > > Setting NEW in an AFTER update or insert trigger is not wise. Try using a before >update trigger instead. > I still get the error message. > -Original Message- > From: Joseph Shraibman [SMTP:[EMAIL PROTECTED]] > Sent: Tuesday, February 06, 2001 6:35 PM > To: [EMAIL PROTECTED] > Subject:plpgsql error: cache lookup from pg_proc failed > > playpen=# create table aa( > playpen(# a int, > playpen(# b int, > playpen(# t timestamp > playpen(# ); > CREATE > playpen=# > playpen=# > playpen=# CREATE FUNCTION touch () RETURNS OPAQUE AS ' > playpen'# BEGIN > playpen'# new.t := current_timestamp; > playpen'# RETURN new; > playpen'# END; > playpen'# ' LANGUAGE 'plpgsql'; > CREATE > playpen=# > playpen=# > playpen=# CREATE TRIGGER lastmod_1 AFTER update or insert ON aa FOR EACH > ROW EXECUTE PROCEDURE touch(); > CREATE > playpen=# insert into aa (a, b) values (1,2); > ERROR: plpgsql: cache lookup from pg_proc failed > > What does this error message mean? > -- Joseph Shraibman [EMAIL PROTECTED] Increase signal to noise ratio. http://www.targabot.com
[SQL] count() and multiple tables
I want to select all the entries from d that have at least one corresponding entry in u that meets my conditions. The problem is that count(*) is returning the number of corresponding entries in u, and I want only the number of entries in d. How do I do this? create table d( id int primary key, status int default 1 ); create table a( key int primary key, status int default 1 ); create table u( dkey int not null, akey int not null, b bool DEFAULT false, status int default 1, primary key (dkey, akey) ); insert into d values (1, 2); insert into a values (1, 3); insert into a values (2, 3); insert into a values (3, 3); insert into u values(1,1,false,2); insert into u values(1,2,false,1); insert into u values(1,3,false,2); select count(*) from d where status = 2 and d.id = u.dkey and u.status = 2 and not u.b and u.akey = a.key and a.status = 3; /* that returns 2 when I want it to return 1 */ drop table d; drop table a; drop table u; -- Joseph Shraibman [EMAIL PROTECTED] Increase signal to noise ratio. http://www.targabot.com ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Select very slow...
David Olbersen wrote: > > On Sun, 18 Mar 2001, Fernando Eduardo B. L. e Carvalho wrote: > > > select p.city,count(*) from sales s, person p where s.doc = p.doc > > group by p.city; > > > >Anyone help-me? > > 1: VACUUM ANALYZE sales >VACUUM ANALYZE person; > > 2: That 'count(*)' is going to be slow. >Try counting a column that's indexed (p.doc might work?) > I don't think that is true. -- Joseph Shraibman [EMAIL PROTECTED] Increase signal to noise ratio. http://www.targabot.com ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [SQL] count() and multiple tables
Stephan Szabo wrote: > > On Mon, 19 Mar 2001, Joseph Shraibman wrote: > > > I want to select all the entries from d that have at least one > > corresponding entry in u that meets my conditions. The problem is that > > count(*) is returning the number of corresponding entries in u, and I > > want only the number of entries in d. How do I do this? > > > > > > create table d( > >id int primary key, > > status int default 1 > > ); > > > > create table a( > >key int primary key, > >status int default 1 > > ); > > > > create table u( > > dkey int not null, > > akey int not null, > > b bool DEFAULT false, > > status int default 1, > > primary key (dkey, akey) > > ); > > > > insert into d values (1, 2); > > > > insert into a values (1, 3); > > insert into a values (2, 3); > > insert into a values (3, 3); > > > > insert into u values(1,1,false,2); > > insert into u values(1,2,false,1); > > insert into u values(1,3,false,2); > > > > select count(*) from d where status = 2 and d.id = u.dkey and u.status = > > 2 and not u.b and u.akey = a.key and a.status = 3; > > And postgres tries to be helpful again... :( [I *really* dislike this > adding to from list thing] Technically the above should be illegal > because no from list contains u or a. Postgres is adding them to the > from list for you. > I get the same result if I do: select count(d.id) from d where status = 2 and d.id = u.dkey and u.status = 2 and not u.b and u.akey = a.key and a.status = 3; So in standard SQL all the tables you join accross are required to be in the FROM? > I think you want something like (untested): > select count(*) from d where status=2 and > exists ( > select * from u, a where u.dkey=d.id and u.status=2 and > no u.b and u.akey=a.key and a.status=3 > ); That works, but I thought there might be a better way because it looks like that will get all the data out of the table and throw it away right after. -- Joseph Shraibman [EMAIL PROTECTED] Increase signal to noise ratio. http://www.targabot.com ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] VACUUM kills Index Scans ?!
Gerald Gutierrez wrote: > > >There is an undocumented little factoid here: CREATE INDEX will update > >(some of) the planner stats, but only if it finds some data in the > >table. CREATE INDEX on an empty table leaves the initial default > >numbers alone. This may be contributing to your confusion, but it was > >deemed necessary ... > > I understand now; it makes sense. I'll be using a number of tables that are > initially very small, perhaps 5 or 10 records. But I expect that the tables > will grow very quickly to several tens (or hundreds) of thousands of > records. It seems reasonable to me that the table should then be set up to > use index scan right from the beginning so that as the table grows the > index scan will become more useful. Thus, the correct sequence for me is > probably: > > > CREATE TABLE > > CREATE INDEX > > load data > no, the correct sequence is to create the index last, which will create statistics that will tell postgres if it really wants to use an index or not. Don't try and second guess postgres. Even better do a VACUUM ANALYZE. -- Joseph Shraibman [EMAIL PROTECTED] Increase signal to noise ratio. http://www.targabot.com ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] exists
I want to select a boolean if there exists a row in another table that matches this one. So I did select ..., (select count(*) from table2 where ...) > 0 ... but that count(*) was taking forever. I know there is a better way to do it, but whenever I try to use EXISTS I get a syntax error. What is the proper way? -- Joseph Shraibman [EMAIL PROTECTED] Increase signal to noise ratio. http://www.targabot.com ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] exists
Thank you, I was missing the parens. If I do an explain I see: -> Index Scan using m_u_and_p_key on m (cost=0.00..3035.22 rows=1363 width=12) even if I put a limit 1 on the select. Why is that? Stephan Szabo wrote: > On Mon, 20 Aug 2001, Joseph Shraibman wrote: > > >>I want to select a boolean if there exists a row in another table that matches this >one. >>So I did select ..., (select count(*) from table2 where ...) > 0 ... >>but that count(*) was taking forever. I know there is a better way to do it, but >whenever >>I try to use EXISTS I get a syntax error. What is the proper way? >> > > Hmm, on current sources I can do: > select ..., exists (select * from table2 where ...) from table1; > > I don't know if that's new though... > -- Joseph Shraibman [EMAIL PROTECTED] Increase signal to noise ratio. http://www.targabot.com ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] exists
Stephan Szabo wrote: >>Limit (cost=48.39..48.39 rows=1 width=70) >> -> Sort (cost=48.39..48.39 rows=2 width=70) >> -> Hash Join (cost=18.46..48.38 rows=2 width=70) >> -> Index Scan using u_p_key on u (cost=0.00..27.66 rows=48 width=28) >> -> Hash (cost=18.39..18.39 rows=28 width=42) >> -> Seq Scan on d (cost=0.00..18.39 rows=28 width=42) >> SubPlan >> -> Nested Loop (cost=0.00..4.04 rows=1 width=20) >> -> Index Scan using a_pkey on a (cost=0.00..2.01 rows=1 >width=4) >> -> Index Scan using p_pkey on pu (cost=0.00..2.02 rows=1 >width=16) >> -> Index Scan using m_u_and_p_key on m (cost=0.00..3035.22 >rows=1363 >>width=44) >> > > At least, what was the query that generated this and is it running > slowly or otherwise giving problems? The total explain doesn't seem > unreasonable to my relatively untrained eyes in the absense of knowing the > query :) > Well the total cost should be at least as big as the sub-costs, no? Doesn't that seem strange? -- Joseph Shraibman [EMAIL PROTECTED] Increase signal to noise ratio. http://www.targabot.com ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] exists
Stephan Szabo wrote:
> On Tue, 21 Aug 2001, Joseph Shraibman wrote:
>
>
>>Thank you, I was missing the parens.
>>
>>If I do an explain I see:
>>
>>-> Index Scan using m_u_and_p_key on m (cost=0.00..3035.22 rows=1363 width=12)
>>
>>
>>even if I put a limit 1 on the select. Why is that?
>>
>
> Is that the inner query (on the exists) or the entire explain?
Just the inner query
>
> I guess it'd be useful to see the whole query and explain and maybe
> schema.
That's big and complicated. Can you reproduce this somewhere else?
Here is the whole explain:
Limit (cost=48.39..48.39 rows=1 width=70)
-> Sort (cost=48.39..48.39 rows=2 width=70)
-> Hash Join (cost=18.46..48.38 rows=2 width=70)
-> Index Scan using u_p_key on u (cost=0.00..27.66 rows=48 width=28)
-> Hash (cost=18.39..18.39 rows=28 width=42)
-> Seq Scan on d (cost=0.00..18.39 rows=28 width=42)
SubPlan
-> Nested Loop (cost=0.00..4.04 rows=1 width=20)
-> Index Scan using a_pkey on a (cost=0.00..2.01 rows=1
width=4)
-> Index Scan using p_pkey on pu (cost=0.00..2.02 rows=1
width=16)
-> Index Scan using m_u_and_p_key on m (cost=0.00..3035.22
rows=1363
width=44)
I tried to make a simple test case:
create table ta (a int);
create table tb (c int, b int);
insert into ta values (2);
insert into ta values (4);
insert into ta values (6);
insert into ta values (8);
insert into ta values (10);
insert into tb values (1,1);
insert into tb values (2,2);
insert into tb values (3,3);
insert into tb values (4,4);
vacuum analyze;
select c, b , exists(select a from ta where a = c) from tb;
explain select c, b , exists(select a from ta where a = c) from tb;
drop table ta;
drop table tb;
... but the data is so small it uses a seq scan:
Seq Scan on tb (cost=0.00..1.04 rows=4 width=8)
SubPlan
-> Seq Scan on ta (cost=0.00..1.06 rows=1 width=4)
>
>
>>Stephan Szabo wrote:
>>
>>>On Mon, 20 Aug 2001, Joseph Shraibman wrote:
>>>
>>>
>>>
>>>>I want to select a boolean if there exists a row in another table that matches
>this one.
>>>>So I did select ..., (select count(*) from table2 where ...) > 0 ...
>>>>but that count(*) was taking forever. I know there is a better way to do it, but
>whenever
>>>>I try to use EXISTS I get a syntax error. What is the proper way?
>>>>
>>>>
>>>Hmm, on current sources I can do:
>>>select ..., exists (select * from table2 where ...) from table1;
>>>
>>>I don't know if that's new though...
>>>
--
Joseph Shraibman
[EMAIL PROTECTED]
Increase signal to noise ratio. http://www.targabot.com
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://www.postgresql.org/search.mpl
Re: [SQL] exists
Then why does the explain say rows=1363 ? I don't mean to nitpick here, but maybe this is the symptom of a larger problem. Tom Lane wrote: > Joseph Shraibman <[EMAIL PROTECTED]> writes: > >>Well the total cost should be at least as big as the sub-costs, no? >> > > Not if the sub-plan in question is for an EXISTS. The sub-plan cost > is stated in terms of cost to retrieve all rows --- but the outer level > EXISTS isn't going to retrieve all rows, it's going to stop as soon as > it gets even one. So the cost estimate that propagates up is > 3035.22/1363. > > BTW, this sort of consideration is why 7.0 and later state plan costs > in terms of startup and total cost: if a plan has a nontrivial startup > cost, just dividing total cost by number of tuples isn't a good way to > estimate the costs of partial retrieval. Really the cost estimate is > figured as > startup_cost + (total_cost-startup_cost) * tuples_retrieved/total_tuples. > This is important for EXISTS, LIMIT, and maybe a couple other things. > Without this, we'd not be bright enough to choose fast-startup plans > over least-total-cost plans in cases where fast-startup is what you want. > > regards, tom lane > -- Joseph Shraibman [EMAIL PROTECTED] Increase signal to noise ratio. http://www.targabot.com ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] exists
Why does explain show more than one row, even if there is a LIMIT = 1? -- Joseph Shraibman [EMAIL PROTECTED] Increase signal to noise ratio. http://www.targabot.com ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] exists
I'm running 7.1.3. What does 'rows=1' mean? The number of rows returned or the number postgres has to look through? Tom Lane wrote: > Joseph Shraibman <[EMAIL PROTECTED]> writes: > >>Why does explain show more than one row, even if there is a LIMIT = 1? >> > > What version are you running? I get results like > > regression=# explain select * from tenk1 limit 1; > NOTICE: QUERY PLAN: > > Limit (cost=0.00..0.03 rows=1 width=148) > -> Seq Scan on tenk1 (cost=0.00..333.00 rows=1 width=148) > > EXPLAIN > > which seems at least moderately self-explanatory. > > regards, tom lane > -- Joseph Shraibman [EMAIL PROTECTED] Increase signal to noise ratio. http://www.targabot.com ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] WHERE on an alias
playpen=# select a, sum(b) as dsum from taba where dsum > 5 group by(a); ERROR: Attribute 'dsum' not found Why can we GROUP BY on an alias but not do a WHERE on an alias? I have a subselect that explain shows is being run twice if I have to put it in the WHERE clause. -- Joseph Shraibman [EMAIL PROTECTED] Increase signal to noise ratio. http://www.targabot.com ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] WHERE on an alias
How do you do a subselect in the from clause? Tom Lane wrote: > Joseph Shraibman <[EMAIL PROTECTED]> writes: > >>playpen=# select a, sum(b) as dsum from taba where dsum > 5 group by(a); >>ERROR: Attribute 'dsum' not found >> > >>Why can we GROUP BY on an alias but not do a WHERE on an alias? >> > > Because WHERE is computed before the select's output list is. > > Strictly speaking you shouldn't be able to GROUP on an alias either (the > SQL spec doesn't allow it). We accept that for historical reasons only, > ie, our interpretation of GROUP used to be wrong and we didn't want to > break applications that relied on the wrong interpretation. > > Note that writing a GROUP on an alias does *not* mean the alias is only > computed once. It saves no computation, only writing out the expression > twice. > > >>I have a subselect that >>explain shows is being run twice if I have to put it in the WHERE clause. >> > > Possibly you could restructure your query into something with a > subselect in the FROM clause? > > regards, tom lane > -- Joseph Shraibman [EMAIL PROTECTED] Increase signal to noise ratio. http://www.targabot.com ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] WHERE on an alias
Josh Berkus wrote: > Joseph, > > >>How do you do a subselect in the from clause? >> > > Assuming that you are using 7.1.0 or higher: > > SELECT tbla.a, tbla.b, total_b > FROM tbla, >(SELECT b, sum(f) as total_b FROM tblb GROUP BY b) b_tot > WHERE tbla.b = b_tot.b > OK my query was like: select u.field1, ... , d.field1, ... (select ml.field from ml WHERE ml.key = u.key order by ml.keyfield desc limit 1) as lastml FROM utable u, dtable d, WHERE u.key = d.key and ... and (sublectect again) = 2; OK when I tried to convert the query: select u.field1, ... , d.field1, ..., mll.lastml FROM utable u, dtable d,(select ml.field as lastml from ml WHERE ml.key = u.key order by ml.keyfield desc limit 1) mll WHERE u.key = d.key and ... and lastml = 2; I got: ERROR: Relation 'u' does not exist The key fields in the subselect must match up with the key fields in the outside query. I'm afraid this would indicate that they would not match up (even if I didn't use short aliases for the tables) and the result of the subselect would be meaningless. -- Joseph Shraibman [EMAIL PROTECTED] Increase signal to noise ratio. http://www.targabot.com ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] WHERE on an alias
Josh Berkus wrote:
> Joseph,
>
> Please take a look at my example again:
>
>
>>>SELECT tbla.a, tbla.b, total_b
>>>FROM tbla,
>>> (SELECT b, sum(f) as total_b FROM tblb GROUP BY b) b_tot
>>>WHERE tbla.b = b_tot.b
>>>
>>>
>
>>OK when I tried to convert the query:
>>select u.field1, ... , d.field1, ..., mll.lastml FROM utable u,
>>dtable d,(select ml.field
>>as lastml from ml WHERE ml.key = u.key order by ml.keyfield desc
>>limit 1) mll WHERE u.key
>>= d.key and ... and lastml = 2;
>>
>
> You need to put the JOIN condition from the subselect ("ml.key = u.key")
> OUTSIDE the subselect ("ml.key = mll.key"). You can't reference columns
> from the main select inside a subselect except in an EXISTS clause.
Actually I do it all the time, in the select part.
>
> This does mean that you're going to need some approach other than the
> "limit 1" to limit the rows in your result set. Probably at GROUP BY.
Well that is the problem. In my subselect I only want the latest value of ml.field,
which
I get my ordering my ml.keyfield. I don't see how group by could help. I would need
to
do something like: ... WHERE mll.keyfield = (select ml.keyfield from mltable ml where
...
order by ml.keyfield desc limit 1) which would be a subselect again which is what I'm
trying to avoid.
SQL is really annoying.
>
> -Josh
>
> P.S. I'm sure you don't mean for your e-mails to come across as
> antagonistic when you are asking for help. Can you please be careful of
> your phrasing?
I certainly didn't mean to be antagonistic, and looking at the email I sent I don't
see
why you thought it was.
--
Joseph Shraibman
[EMAIL PROTECTED]
Increase signal to noise ratio. http://www.targabot.com
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] WHERE on an alias
Stephan Szabo wrote: > > > I think you'd want to move the entire query excepting the lastml where > condition into a single subselect in the outer from with the lastml > condition on the outside: > select * from () as blah where > lastml=2; > > However, I don't think this changes the computation that it's doing > (simple example explains still show two subquery runs). > Yep, that works. I wasn't familiar with how the subselect in the from part works. But as you say an explain still shows it doing the index scan twice. From the postgres docs: A sub-SELECT can appear in the FROM clause. This acts as though its output were created as a temporary table for the duration of this single SELECT command. Note that the sub-SELECT must be surrounded by parentheses, and an alias must be provided for it. So does postgres actually use a temporary table behind the scenses? It appears not. -- Joseph Shraibman [EMAIL PROTECTED] Increase signal to noise ratio. http://www.targabot.com ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [SQL] WHERE on an alias
Except I want ml.field, which is a data field, not a key. So I can't group by it. Bascially the point of the subselect is to get the field value where serial is hightest and the two keys match. Josh Berkus wrote: > Joseph, > > >>select d.field1, d.field2, u.field1, u.field2, (select ml.field from >>mltable where ml.key1 >>= u.key1 and ml.key2 = u.key2 order by ml.serial desc limit 1) from >>utable u, dtable d, >>where u.key1 = d.key order by d.somefield limit 25 offset ???; >> > >>From the look of it, you want to simply take the utable references out > of the subselect. Try: > > SELECT d.field1, d.field2, u.field1, u.field2, mll.latest > FROM utable u, dtable d, > (SELECT key1, key2, max(serial) as latest > FROM mltable > GROUP BY key1, key2) mll > WHERE u.key1 = d.key > AND u.key1 = mll.key1 and u.key2 = mll.key2 > ORDER BY d.somefield > LIMIT 25 OFFSET $pageno > > That should give you the utable and dtable records, plus the last serial > value while executing the subselect only once per query call. Give it a > spin. > > -Josh > > > __AGLIO DATABASE SOLUTIONS___ >Josh Berkus > Complete information technology [EMAIL PROTECTED] >and data management solutions (415) 565-7293 > for law firms, small businessesfax 621-2533 > and non-profit organizations. San Francisco > -- Joseph Shraibman [EMAIL PROTECTED] Increase signal to noise ratio. http://www.targabot.com ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] WHERE on an alias
Actually I think I discovered part of the problem. My subquery did: ORDER BY serial DESC LIMIT 1 where serial happened to be the primary key. The explain showed that it was doing an index search on this serial. So I did the order by on a date field that doesn't have an index, so the query used a different index to do the query. The different index that it used was on the two keys that the mltable shares with the utable, so the query went much faster even though the explain estimated it taking longer. I do a vacuum analyze each night in the cron. More info: the utable is uniqe on two fields. The mltable has these coresponding key fields in it, but there is more than one entry that has the same field combination. I have an index on these because I knew I would want to get the entries by these two fields. The explain for the fast query shows: -> Sort (cost=3106.17..3106.17 rows=1363 width=10) -> Index Scan using mltabke_u_and_p_key on mltable ml (cost=0.00..3035.22 rows=1363 width=10) While the explain for the slow one is: -> Index Scan Backward using mltable_pkey on mltable ml (cost=0.00..28794.49 rows=1363 width=6) I don't know why the planner thought the sort would be so expensive. Stephan Szabo wrote: > On Mon, 27 Aug 2001, Joseph Shraibman wrote: > > >>Stephan Szabo wrote: >> >>>I think you'd want to move the entire query excepting the lastml where >>>condition into a single subselect in the outer from with the lastml >>>condition on the outside: >>> select * from () as blah where >>> lastml=2; >>> >>>However, I don't think this changes the computation that it's doing >>>(simple example explains still show two subquery runs). >>> >>> >>Yep, that works. I wasn't familiar with how the subselect in the from part works. >But as >>you say an explain still shows it doing the index scan twice. >> >> From the postgres docs: >> >> A sub-SELECT can appear in the FROM clause. This acts as though its output >were >>created as a temporary table for the duration of this single SELECT command. Note >that the >>sub-SELECT must be surrounded by parentheses, and an alias must be provided for it. >> >>So does postgres actually use a temporary table behind the scenses? It appears not. >> > > I think what's happening is that the where condition is being pushed down > into the subselect because usually that's the correct optimization, since > you'd normally want > select col1 from (select col1 from foo) as foo2 where col1=2; > to do an index scan on foo. > -- Joseph Shraibman [EMAIL PROTECTED] Increase signal to noise ratio. http://www.targabot.com ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [SQL] WHERE on an alias
Josh Berkus wrote: > Joseph, > > >>select d.field1, d.field2, u.field1, u.field2, (select ml.field from >>mltable where ml.key1 >>= u.key1 and ml.key2 = u.key2 order by ml.serial desc limit 1) from >>utable u, dtable d, >>where u.key1 = d.key order by d.somefield limit 25 offset ???; >> > >>From the look of it, you want to simply take the utable references out > of the subselect. Try: > > SELECT d.field1, d.field2, u.field1, u.field2, mll.latest > FROM utable u, dtable d, > (SELECT key1, key2, max(serial) as latest > FROM mltable > GROUP BY key1, key2) mll > WHERE u.key1 = d.key > AND u.key1 = mll.key1 and u.key2 = mll.key2 > ORDER BY d.somefield > LIMIT 25 OFFSET $pageno > > That should give you the utable and dtable records, plus the last serial > value while executing the subselect only once per query call. Give it a > spin. > OK I tried a variant on this of doing select ml.field from (sublect) mll WHERE ml.field in(values) and mll.serial = ml.serial. I am now doing two queries, but the second one is a simple index query that returns rather fast. The first, however, is horendous. I have thousands of entries in the utable, and this subquery is getting all thoses thousands of corresponding max(serial) values from the mltable before throwing all but the 25 that this query needs away. I didn't spell out exactly how all my data is aranged so you didn't know this would happend, but otherwise it might have worked. Or maybe postgres should have been smart enough to eliminate the other values before it did the group by > -Josh > > > __AGLIO DATABASE SOLUTIONS___ >Josh Berkus > Complete information technology [EMAIL PROTECTED] >and data management solutions (415) 565-7293 > for law firms, small businessesfax 621-2533 > and non-profit organizations. San Francisco > -- Joseph Shraibman [EMAIL PROTECTED] Increase signal to noise ratio. http://www.targabot.com ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] WHERE on an alias
Josh Berkus wrote: > Joseph, > > >>Actually I do it all the time, in the select part. >> > > Hmm. Frankly, I didn't know that Subselects in the field list were > supported, so this is a new one on me. > > > >>Well that is the problem. In my subselect I only want the latest >>value of ml.field, which >>I get my ordering my ml.keyfield. I don't see how group by could >>help. I would need to >>do something like: ... WHERE mll.keyfield = (select ml.keyfield from >>mltable ml where ... >>order by ml.keyfield desc limit 1) which would be a subselect again >>which is what I'm >>trying to avoid. >> >>SQL is really annoying. >> > > If you think SQL is annoying, you should try relational calculus ;-) > > Think you can provide me a simplified version of your table structure > and the results you're trying to get out of your query? I think that > your problem is solvable with a little nested subselect, but I'm having > a lot of difficulty picturing what we're looking at. > Basically there is the utable, which has two keys in it. I want to select some rows from the utable. Some columns come from the dtable, which has a key that matches to one of the utable keys, and I want to select the last value from the mltable which has the same two keys as utable and it has a serial value that I use do determine which is the last entry. I also want to use the last value from the mltable in the WHERE clause. So the select is basically select d.field1, d.field2, u.field1, u.field2, (select ml.field from mltable where ml.key1 = u.key1 and ml.key2 = u.key2 order by ml.serial desc limit 1) from utable u, dtable d, where u.key1 = d.key order by d.somefield limit 25 offset ???; The tricky part is the mltable. I don't want to put the whole subselect into the where clause because the mltable lookup is the most expensive part. And I need to have the limit and offset be done by the entries in the utable/dtable. -- Joseph Shraibman [EMAIL PROTECTED] Increase signal to noise ratio. http://www.targabot.com ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] WHERE on an alias
I'm not clear how this helps. I want to get the last entry of ml. The distinct on means I won't get duplicate entries with the same key values, but what specifies that I'm getting the last ml value, or even one ml value at all? Tom Lane wrote: > Joseph Shraibman <[EMAIL PROTECTED]> writes: > >>Basically there is the utable, which has two keys in it. I want to >>select some rows from the utable. Some columns come from the dtable, >>which has a key that matches to one of the utable keys, and I want to >>select the last value from the mltable which has the same two keys as >>utable and it has a serial value that I use do determine which is the >>last entry. I also want to use the last value from the mltable in the >>WHERE clause. >> > > I'm thinking you could use a subselect along the lines of > > select distinct on (ml.f1,ml.f2) * from ut,ml > where ut.f1 = ml.f1 and ut.f2 = ml.f2 > order by ml.f1, ml.f2, ml.f3 desc; > > f1 and f2 being the match keys and f3 being the serial value. > Given two-key indexes, I get plans along the lines of > > Unique (cost=109.96..110.08 rows=2 width=20) > -> Sort (cost=109.96..109.96 rows=25 width=20) > -> Merge Join (cost=0.00..109.38 rows=25 width=20) > -> Index Scan using ut_pkey on ut (cost=0.00..52.00 rows=1000 >width=8) > -> Index Scan using ml_f1f2 on ml (cost=0.00..52.00 rows=1000 >width=12) > > which doesn't look too bad. > > regards, tom lane > -- Joseph Shraibman [EMAIL PROTECTED] Increase signal to noise ratio. http://www.targabot.com ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] WHERE on an alias
If I try to put a distinct on in my subselect int the from I get: ERROR: SELECT DISTINCT ON expressions must match initial ORDER BY expressions what does that mean? Tom Lane wrote: > Joseph Shraibman <[EMAIL PROTECTED]> writes: > >>Basically there is the utable, which has two keys in it. I want to >>select some rows from the utable. Some columns come from the dtable, >>which has a key that matches to one of the utable keys, and I want to >>select the last value from the mltable which has the same two keys as >>utable and it has a serial value that I use do determine which is the >>last entry. I also want to use the last value from the mltable in the >>WHERE clause. >> > > I'm thinking you could use a subselect along the lines of > > select distinct on (ml.f1,ml.f2) * from ut,ml > where ut.f1 = ml.f1 and ut.f2 = ml.f2 > order by ml.f1, ml.f2, ml.f3 desc; > > f1 and f2 being the match keys and f3 being the serial value. > Given two-key indexes, I get plans along the lines of > > Unique (cost=109.96..110.08 rows=2 width=20) > -> Sort (cost=109.96..109.96 rows=25 width=20) > -> Merge Join (cost=0.00..109.38 rows=25 width=20) > -> Index Scan using ut_pkey on ut (cost=0.00..52.00 rows=1000 >width=8) > -> Index Scan using ml_f1f2 on ml (cost=0.00..52.00 rows=1000 >width=12) > > which doesn't look too bad. > > regards, tom lane > -- Joseph Shraibman [EMAIL PROTECTED] Increase signal to noise ratio. http://www.targabot.com ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] group by weirdness
Could someome explain these error messages to me? Why am I being asked to group by j.id? And why is the subquery worried about ml.oid if ml.oid is used in an aggregate? Follows: script, then output. select version(); create table j (id int, created timestamp default current_timestamp, fkey int); create table mj (jid int, mid int); create table ml (jid int, created timestamp default current_timestamp, state int); insert into j (id, fkey) values (1, 1); insert into j (id, fkey) values (2, 1); insert into mj values(1, 1); insert into mj values(1, 2); insert into mj values(2, 3); insert into mj values(2, 4); insert into mj values(2, 5); insert into ml(jid, state) values (1, 2); insert into ml(jid, state) values (1, 2); insert into ml(jid, state) values (1, 2); insert into ml(jid, state) values (1, 2); insert into ml(jid, state) values (1, 11); insert into ml(jid, state) values (2, 2); insert into ml(jid, state) values (2, 2); insert into ml(jid, state) values (2, 11); select j.id, j.created, count(mj.mid), (select count(ml.oid) where ml.state <> 11), (select count(ml.oid) where ml.state IN(2,5) ) FROM j, mj, ml WHERE j.fkey = 1 AND mj.jid = j.id AND ml.jid = j.id ; select j.id, j.created, count(mj.mid), (select count(ml.oid) where ml.state <> 11), (select count(ml.oid) where ml.state IN(2,5) ) FROM j, mj, ml WHERE j.fkey = 1 AND mj.jid = j.id AND ml.jid = j.id group by j.id, j.created; drop table j; drop table mj ; drop table ml; === playpen=# select version(); version - PostgreSQL 7.1.3 on i686-pc-linux-gnu, compiled by GCC egcs-2.91.66 (1 row) playpen=# create table j (id int, created timestamp default current_timestamp, fkey int); CREATE playpen=# create table mj (jid int, mid int); CREATE playpen=# create table ml (jid int, created timestamp default current_timestamp, state int); CREATE playpen=# playpen=# insert into j (id, fkey) values (1, 1); playpen=# insert into ml(jid, state) values (2, 11); INSERT 329676 1 playpen=# playpen=# select j.id, j.created, count(mj.mid), playpen-# (select count(ml.oid) where ml.state <> 11), playpen-# (select count(ml.oid) where ml.state IN(2,5) ) playpen-# FROM j, mj, ml WHERE j.fkey = 1 AND mj.jid = j.id AND ml.jid = j.id ; ERROR: Attribute j.id must be GROUPed or used in an aggregate function playpen=# playpen=# select j.id, j.created, count(mj.mid), playpen-# (select count(ml.oid) where ml.state <> 11), playpen-# (select count(ml.oid) where ml.state IN(2,5) ) playpen-# FROM j, mj, ml WHERE j.fkey = 1 AND mj.jid = j.id AND ml.jid = j.id playpen-# group by j.id, j.created; ERROR: Sub-SELECT uses un-GROUPed attribute ml.oid from outer query playpen=# playpen=# drop table j; DROP playpen=# drop table mj ; DROP playpen=# drop table ml; DROP -- Joseph Shraibman [EMAIL PROTECTED] Increase signal to noise ratio. http://www.targabot.com ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] group by weirdness
Josh Berkus wrote: > Joseph, > > The subject line could describe a lot of what I see outside my house > every day (I live in San Francisco CA). > > >>Could someome explain these error messages to me? Why am I being >>asked to group by j.id? >> > > Because you've asked the db engine to count on mj.mid. The parser want > you to be specific about whether the other columns are being aggregated > or not. But they are in seperate tables, so how could it think they could be aggregated together? -- Joseph Shraibman [EMAIL PROTECTED] Increase signal to noise ratio. http://www.targabot.com ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] group by weirdness
Josh Berkus wrote: > Try putting your sub-selects in the FROM clause instead. (Personally, > I've never found a use for sub-selects in the SELECT clause) > > SELECT j.id, j.created, count(mj.mid), ma1.mcount, ma2.mcount > FROM j, mj, > (SELECTjid, COUNT(oid) as mcount FROM ml > WHERE ml.state <> 11 GROUP BY jid) ma1, > (SELECT jid, COUNT(oid) as mcount FROM ml > WHERE ml.state in (2,5) GROUP BY jid) ma2 > WHERE j.fkey = 1 AND mj.jid = j.id > AND ma1.jid = j.id AND ma2.jid = j.id > GROUP BY j.id, j.created, ma1.mcount, ma2.mcount; > OK that worked for this simple example, but on my real database the performance was horrible, and it didn't work for then there were zero entries in ml (this bites me sometimes, when the AND clause keeps things from working as I think they should). Putting the selects in the SELECT solved both problems. I took out the 'AND ml.jid = j.id' from the outer WHERE (would have also excluded cases where there were zero entries in ml) and only refrenced ml in the subselect. Thanks for your help. -- Joseph Shraibman [EMAIL PROTECTED] Increase signal to noise ratio. http://www.targabot.com ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] When will vacuum go away?
BTW will there be a 7.1.4 release before 7.2 comes out so we can dump our databases to upgrade to 7.2 w/o there being 60 in the seconds field? Tom Lane wrote: > "Michael Richards" <[EMAIL PROTECTED]> writes: > >>I've been watching for this for some time. First it was 7.0, then >>7.1. Does anyone have any idea on when the row re-use code will be >>ready? >> > > VACUUM isn't disappearing any time soon, but 7.2's version of vacuum > runs in parallel with normal transactions, so it's not so painful to > run it frequently. See discussion in development docs, > http://candle.pha.pa.us/main/writings/pgsql/sgml/maintenance.html > > >>Given trouble with Great Bridge is there any info out there on when >>7.2 might hit the streets? >> > > The last several postponements of 7.2 beta have *not* been the fault > of the ex-GreatBridge folks around here. > > You can find a snapshot that should be pretty durn close to 7.2beta1 > at ftp://ftp2.us.postgresql.org/pub/dev/postgresql-snapshot.tar.gz > (note that at last word, other mirrors were not up to date --- if > the doc/TODO file doesn't contain a date in October, it's stale). > I think the only thing we're still waiting on is some datetime fixes > from Tom Lockhart... > > regards, tom lane > > -------(end of broadcast)--- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org > -- Joseph Shraibman [EMAIL PROTECTED] Increase signal to noise ratio. http://www.targabot.com ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] index question
Lets say I have a table with columns a and b. I want to do a query like SELECT count(distinct b) WHERE a = 2; Should I have an index on a or an index on (a,b)? -- Joseph Shraibman [EMAIL PROTECTED] Increase signal to noise ratio. http://www.targabot.com ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] dropping foreign key
I have to drop a froeign key from one of my tables. The problem is that I have another table that has a foreign key on the first one, so I can't do the select to temp-table thing and move it back. Is there any way I can remove it by mucking with pg's internal tables? -- Joseph Shraibman [EMAIL PROTECTED] Increase signal to noise ratio. http://www.targabot.com ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] dropping foreign key
Can I just set tgenabled to false? Stephan Szabo wrote: > On Tue, 23 Oct 2001, Joseph Shraibman wrote: > > >>I have to drop a froeign key from one of my tables. The problem is that I have >another >>table that has a foreign key on the first one, so I can't do the select to >temp-table >>thing and move it back. >> >>Is there any way I can remove it by mucking with pg's internal tables? >> > > Yeah. All you should need to do is remove the three triggers that > were created for the constraint (1 on the fk table, 2 on the pk table). > You can find them in pg_trigger and you'll need to double quote the > names for the drop trigger statements because they're mixed case. > > -- Joseph Shraibman [EMAIL PROTECTED] Increase signal to noise ratio. http://www.targabot.com ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[SQL] joining from multiple tables
I have a table I want to join on, but the conditions that restrict it span more than one
table. For example:
create table num_tab (thekey int primary key, val int, class char);
create table class_tab (class char primary key, tkey int);
create table txt_tab (thekey int primary key, class int, txt text);
insert into num_tab values (1, 1, 'o');
insert into num_tab values (2, 2, 'e');
insert into num_tab values (3, 3, 'o');
insert into num_tab values (4, 4, 'e');
insert into num_tab values (5, 5, 'o');
insert into num_tab values (6, 6, 'e');
insert into class_tab values('o', 1);
insert into class_tab values('e', 2);
insert into txt_tab values (2, 2,'two');
insert into txt_tab values (4, 2,'four');
insert into txt_tab values (6, 2,'six');
select distinct n.thekey, n.val, t.txt FROM num_tab n LEFT JOIN txt_tab t ON n.thekey =
t.thekey
WHERE n.thekey < 5 AND t.class = class_tab.tkey AND n.class = class_tab.class;
produces:
thekey | val | txt
+-+--
2 | 2 | two
4 | 4 | four
... which is not what we want, because 1,3, and 5 aren't included, but:
select distinct n.thekey, n.val, t.txt FROM num_tab n LEFT JOIN txt_tab t ON n.thekey =
t.thekey AND t.class = class_tab.tkey AND n.class = class_tab.class
WHERE n.thekey < 5;
produces:
NOTICE: Adding missing FROM-clause entry for table "class_tab"
ERROR: JOIN/ON clause refers to "class_tab", which is not part of JOIN
So how do I do this?
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] How to join from two tables at once?
How can I join on one table with join conditions refering to two tables? In this example p is missing an entry that corresponds to u. I want to select from u and p, but have entries in u that don't have an entry in p. The problem is I need to go through table a to get the corresponding value in table p, and a LEFT JOIN only operates on two tables. The subselect works, but in real life turns out to be a big performance drain. - example: begin; create table u (uid int, aid int, txt text); create table a (id int, pkey int); create table p (uid int, pkey int, val text); insert into u VALUES(1,1,'one'); insert into u VALUES(2,1,'two'); insert into u VALUES(3,1,'three'); insert into a VALUES(1, 9); insert into p VALUES(1,9,'ONE'); insert into p VALUES(3,9,'THREE'); -- doesn't get 2, because there is no entry in p for it SELECT u.uid, u.txt, p.val FROM u,a,p WHERE u.uid = p.uid and a.id = u.aid and p.pkey = a.pkey; -- works, but uses a subselect SELECT u.uid, u.txt, (SELECT p.val FROM p WHERE p.uid = u.uid AND p.pkey = a.pkey) FROM u,a WHERE a.id = u.aid; --doesn't work: ERROR: JOIN/ON clause refers to "u", which is not part of JOIN SELECT u.uid, u.txt, p.val FROM u,a LEFT JOIN p ON p.uid = u.uid AND p.pkey = a.pkey WHERE a.id = u.aid; abort; ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] How to join from two tables at once?
Stephan Szabo wrote: Probably you want something like: SELECT u.uid, u.txt, p.val FROM u INNER JOIN a ON (a.id=u.aid) LEFT JOIN p ON (p.uid=u.uid AND p.pkey=a.pkey); From the docs: A CROSS JOIN or INNER JOIN is a simple Cartesian product, the same as you get from listing the two items at the top level of FROM. CROSS JOIN is equivalent to INNER JOIN ON (TRUE), that is, no rows are removed by qualification. These join types are just a notational convenience, since they do nothing you couldn't do with plain FROM and WHERE. ... so obviously there *is* something that INNER JOIN can do that regular ANDs can't. But I'm still not clear why one works and the other doesn't. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] sorting by day of the week
p8:owl=>SELECT to_char( logtime, 'Dy'),count(*) FROM logtab WHERE
date_trunc('day', logtime) > current_date + '7 day ago'::interval group
by to_char( logtime, 'Dy') ORDER BY to_char( logtime, 'Dy') DESC;
to_char | count
-+---
Wed | 1447
Tue | 618
Thu | 1161
Sun | 230
Sat | 362
Mon | 760
Fri | 1281
(7 rows)
The problem is that I want those results sorted in day of week order,
not text order of the day name, so I tried this:
p8:owl=>SELECT to_char( logtime, 'Dy'),count(*) FROM sclog WHERE
date_trunc('day', logtime) > current_date + '7 day ago'::interval group
by to_char( logtime, 'Dy') ORDER BY to_char( logtime, 'D') DESC;
ERROR: column "sclog.logtime" must appear in the GROUP BY clause or be
used in an aggregate function
Now obviously I don't want to group by logtime (a timestamp) so how do I
work around this? What I really need is a function that converts from
the char representation to a day of week number or vice versa. I also
have the same problem with month names.
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] sorting by day of the week
Nevermind, I figured out that I just needed to do it like this:
SELECT to_char( logtime, 'Dy'),count(*),to_char( logtime, 'D') FROM
sclog WHERE date_trunc('day', logtime) > current_date + '7 day
ago'::interval group by to_char( logtime, 'Dy'),to_char( logtime, 'D')
ORDER BY to_char( logtime, 'D') DESC;
It is interesting that I can't put to_char( logtime, 'D') in the the
group by without putting it in the select.
Joseph Shraibman wrote:
p8:owl=>SELECT to_char( logtime, 'Dy'),count(*) FROM logtab WHERE
date_trunc('day', logtime) > current_date + '7 day ago'::interval group
by to_char( logtime, 'Dy') ORDER BY to_char( logtime, 'Dy') DESC;
to_char | count
-+---
Wed | 1447
Tue | 618
Thu | 1161
Sun | 230
Sat | 362
Mon | 760
Fri | 1281
(7 rows)
The problem is that I want those results sorted in day of week order,
not text order of the day name, so I tried this:
p8:owl=>SELECT to_char( logtime, 'Dy'),count(*) FROM sclog WHERE
date_trunc('day', logtime) > current_date + '7 day ago'::interval group
by to_char( logtime, 'Dy') ORDER BY to_char( logtime, 'D') DESC;
ERROR: column "sclog.logtime" must appear in the GROUP BY clause or be
used in an aggregate function
Now obviously I don't want to group by logtime (a timestamp) so how do I
work around this? What I really need is a function that converts from
the char representation to a day of week number or vice versa. I also
have the same problem with month names.
---(end of broadcast)---
TIP 6: explain analyze is your friend
[SQL] possible bug with group by?
Is this a bug or am I just misunderstanding something? playpen=> create table tablea ( a int,b int , c int ); CREATE playpen=> insert into tablea(a, b) values (1 ,2); INSERT 28299 1 playpen=> insert into tablea(a, b, c) values (2 ,3, 4); INSERT 28300 1 playpen=> select a, b, case when c is null then 'not set' else 'set' end as z from tablea; a|b|z -+-+--- 1|2|not set 2|3|set (2 rows) playpen=> select a, b, case when c is null then 'not set' else 'set' end as z from tablea group by a, b, z; ERROR: Unable to identify an operator '<' for types 'unknown' and 'unknown' You will have to retype this query using an explicit cast playpen=>
Re: [SQL] possible bug with group by?
Julie Hunt wrote: > > Joseph Shraibman wrote: > > > > > > > playpen=> select a, b, case when c is null then 'not set' else 'set' end > > as z from tablea group by a, b, z; > > ERROR: Unable to identify an operator '<' for types 'unknown' and > > 'unknown' > > You will have to retype this query using an explicit cast > > playpen=> > > Hi, > > should your group by be a, b, c not a, b, z? > That would work for this example. But shouldn't it work for z also? I discovered that if I replace z with text I don't get that error, but then I can't name my columns.
Re: [SQL] possible bug with group by?
Stephan Szabo wrote:
>
> > Is this a bug or am I just misunderstanding something?
> >
> > playpen=> create table tablea ( a int,b int , c int );
> > CREATE
> > playpen=> insert into tablea(a, b) values (1 ,2);
> > INSERT 28299 1
> > playpen=> insert into tablea(a, b, c) values (2 ,3, 4);
> > INSERT 28300 1
> > playpen=> select a, b, case when c is null then 'not set' else 'set' end
> > as z from tablea;
> > a|b|z
> > -+-+---
> > 1|2|not set
> > 2|3|set
> > (2 rows)
> >
> >
> > playpen=> select a, b, case when c is null then 'not set' else 'set' end
> > as z from tablea group by a, b, z;
> > ERROR: Unable to identify an operator '<' for types 'unknown' and
> > 'unknown'
> > You will have to retype this query using an explicit cast
> > playpen=>
>
> I'm not 100% sure, but my guess would be that it's not certain what
> type 'not set' and 'set' are going to be (hence type 'unknown') and when
> it tries to group it, it's unable to determine how to tell what's greater
> than
> something else.
But why would group by need to sort it? To insert it into a tree to
make lookups of distinct values faster?
>
> As a workaround, you should be able to do something like the following:
> select a,b, case when c is null then cast('not set' as text) else cast('set'
> as text)
> end as z from tablea group by a, b, z;
That does work. Thanks.
[SQL] aliases break my query
These two queries are exactly alike. The first one uses aliases except for the order by. The second uses aliases also for the order by. The third uses whole names. The third has the behavior I want. Someone please tell me what I am doing wrong. I don't want to have to use whole names for my query. The data for the tables are at the end. playpen=> select ta.a,ta.b,ta.c, (select count (tb.zz) where tb.yy = ta.a) from tablea ta, tableb tb order by tablea.a; a|b|c|?column? -+-+-+ 1|2| | 0 2|3|4| 1 3|4|5| 0 4|5|4| 0 1|2| | 0 2|3|4| 1 3|4|5| 0 4|5|4| 0 1|2| | 0 2|3|4| 0 3|4|5| 1 4|5|4| 0 1|2| | 0 2|3|4| 0 3|4|5| 0 4|5|4| 1 1|2| | 0 2|3|4| 0 3|4|5| 0 4|5|4| 0 1|2| | 0 2|3|4| 1 3|4|5| 0 4|5|4| 0 1|2| | 0 2|3|4| 1 3|4|5| 0 4|5|4| 0 1|2| | 0 2|3|4| 0 3|4|5| 1 4|5|4| 0 1|2| | 0 2|3|4| 0 3|4|5| 0 4|5|4| 1 1|2| | 0 2|3|4| 0 3|4|5| 0 4|5|4| 0 1|2| | 0 2|3|4| 1 3|4|5| 0 4|5|4| 0 1|2| | 0 2|3|4| 1 3|4|5| 0 4|5|4| 0 1|2| | 0 2|3|4| 0 3|4|5| 1 4|5|4| 0 1|2| | 0 2|3|4| 0 3|4|5| 0 4|5|4| 1 1|2| | 0 2|3|4| 0 3|4|5| 0 4|5|4| 0 1|2| | 0 2|3|4| 1 3|4|5| 0 4|5|4| 0 1|2| | 0 2|3|4| 1 3|4|5| 0 4|5|4| 0 1|2| | 0 2|3|4| 0 3|4|5| 1 4|5|4| 0 1|2| | 0 2|3|4| 0 3|4|5| 0 4|5|4| 1 1|2| | 0 2|3|4| 0 3|4|5| 0 4|5|4| 0 (80 rows) playpen=> select ta.a,ta.b,ta.c, (select count (tb.zz) where tb.yy = ta.a) from tablea ta, tableb tb order by ta.a; a|b|c|?column? -+-+-+ 1|2| | 0 1|2| | 0 1|2| | 0 1|2| | 0 1|2| | 0 2|3|4| 1 2|3|4| 1 2|3|4| 0 2|3|4| 0 2|3|4| 0 3|4|5| 0 3|4|5| 0 3|4|5| 1 3|4|5| 0 3|4|5| 0 4|5|4| 0 4|5|4| 0 4|5|4| 0 4|5|4| 1 4|5|4| 0 (20 rows) playpen=> select tablea.a,tablea.b,tablea.c, (select count (tableb.zz) where tableb.yy = tablea.a) order by tablea.a; a|b|c|?column? -+-+-+ 1|2| | 0 2|3|4| 2 3|4|5| 1 4|5|4| 1 (4 rows) playpen=> playpen=> select * from tablea; a|b|c -+-+- 1|2| 2|3|4 3|4|5 4|5|4 (4 rows) playpen=> select * from tableb; yy|zz --+-- 2| 4 2| 5 3| 9 4|14 5|15 (5 rows)
Re: [SQL] aliases break my query
Peter Eisentraut wrote: > > > > playpen=> select ta.a,ta.b,ta.c, (select count (tb.zz) where tb.yy = > > > ta.a) from tablea ta, tableb tb order by tablea.a; > > [ produces 80 rows ] > > > > playpen=> select ta.a,ta.b,ta.c, (select count (tb.zz) where tb.yy = > > > ta.a) from tablea ta, tableb tb order by ta.a; > > [ produces 20 rows ] > > > > playpen=> select tablea.a,tablea.b,tablea.c, (select count (tableb.zz) > > > where tableb.yy = tablea.a) order by tablea.a; > > [ produces 4 rows ] > > Once again, I think that we *really* need to discuss whether implicit > range table entries in SELECT are a good idea. What is an "implicit range table entry"? We invariably get a > question like this every week and invariably the answer is "if you give a > table an alias you *must* refer to it by that alias". Hey, I *did* do that in the second query, and that still produced extra results. I tried putting the aliases in the inner select too but that didn't help. In fact the inner select always is 4 in that case. Unless I only alias tableb in the inner query, and let it get the definition of tablea from the outer query. (I'm sure Tom has > this reply automated by now.) I claim the only thing that buys is > confusion for very little convenience at the other end. > > Stop the madness! :) > > -- > Peter Eisentraut Sernanders väg 10:115 > [EMAIL PROTECTED] 75262 Uppsala > http://yi.org/peter-e/Sweden
Re: [SQL] aliases break my query
Tom Lane wrote: > > Joseph Shraibman <[EMAIL PROTECTED]> writes: > > These two queries are exactly alike. The first one uses aliases except > > for the order by. The second uses aliases also for the order by. The > > third uses whole names. The third has the behavior I want. > > I think you are confusing yourself by leaving out FROM clauses. > In particular, with no FROM for the inner SELECT it's not real clear > what should happen there. I can tell you what *is* happening, but > who's to say if it's right or wrong? > Well I assumed that the aliases would be inerited from the outer query. > > playpen=> select ta.a,ta.b,ta.c, (select count (tb.zz) where tb.yy = > > ta.a) from tablea ta, tableb tb order by tablea.a; > [ produces 80 rows ] > > > playpen=> select ta.a,ta.b,ta.c, (select count (tb.zz) where tb.yy = > > ta.a) from tablea ta, tableb tb order by ta.a; > [ produces 20 rows ] > > The difference between these two is that by explicitly specifying > "tablea" in the order-by clause, you've created a three-way join, > as if you had written "from tablea ta, tableb tb, tablea tablea". > Once you write an alias in a from-clause entry, you must refer to > that from-clause entry by its alias, not by its true table name. I guess I made the mistake of assuming that SQL is logical. I don't know what I was thinking. ;) > > Meanwhile, what of the inner select? It has no FROM clause *and* > no valid table names. The only way to interpret the names in it > is as references to the outer select. So, on any given iteration > of the outer select, the inner select collapses to constants. > It looks like "SELECT count(constant1) WHERE constant2 = constant3" > and so you get either 0 or 1 depending on whether tb.yy and ta.a > from the outer scan are different or equal. OK that sorta makes sense to be. What I want is the behavior I got with the third query (below). I want the values in table a, and then a count of how many entries in tableb have the yy field of tableb that matches that entry in tablea's a field. playpen=> select ta.a,ta.b,ta.c, (select count (tb.zz) from tableb tb where tb.yy = ta.a) from tablea ta, tableb tb group by ta.a, ta.b, ta.c order by ta.a; a|b|c|?column? -+-+-+ 1|2| | 0 2|3|4| 2 3|4|5| 1 4|5|4| 1 (4 rows) ... which is what I want. Thanks. > > > playpen=> select tablea.a,tablea.b,tablea.c, (select count (tableb.zz) > > where tableb.yy = tablea.a) order by tablea.a; > [ produces 4 rows ] > > Here the outer select is not a join at all --- it mentions only tablea, > so you are going to get one output for each tablea row. The inner > select looks like "select count (zz) FROM tableb WHERE yy = ", > so you get an actual scan of tableb for each iteration of the outer > scan. > > It's not very clear from these examples what you actually wanted to have > happen, but I suggest that you will have better luck if you specify > explicit FROM lists in both the inner and outer selects, and be careful > that each variable you use clearly refers to exactly one of the > FROM-list entries. > > regards, tom lane
[SQL] counting distinct values
Using the example from http://www.postgresql.org/docs/aw_pgsql_book/node59.html, what would I do if I wanted to know the number of different cities where I had a friend in each state? select count(city) group by state; would not work because if you had two friends in the same city it would be counted twice.
Re: [SQL] counting distinct values
Bruce Momjian wrote: > > > Using the example from > > http://www.postgresql.org/docs/aw_pgsql_book/node59.html, what would I > > do if I wanted to know the number of different cities where I had a > > friend in each state? select count(city) group by state; would not work > > because if you had two friends in the same city it would be counted > > twice. > > > > How about? > > select city, state, count(*) group by state, city; OK that doesn't work for me because I want to use other agregates on the state level. Forgetting the state/city example. playpen=> create table tablem ( a int, b int, n int); CREATE playpen=> insert into tablem (a, b, n) values (1, 2, 1); INSERT 35197 1 playpen=> insert into tablem (a, b, n) values (1, 2, 2); INSERT 35198 1 playpen=> insert into tablem (a, b, n) values (1, 3, 1); INSERT 35199 1 playpen=> insert into tablem (a, b, n) values (1, 4, 3); INSERT 35296 1 playpen=> insert into tablem (a, b, n) values (1, 1, 4); INSERT 35297 1 playpen=> insert into tablem (a, b, n) values (2, 3, 3); INSERT 35298 1 playpen=> insert into tablem (a, b, n) values (2, 5, 7); INSERT 35299 1 playpen=> insert into tablem (a, b, n) values (2, 3, 3); INSERT 35300 1 playpen=> insert into tablem (a, b, n) values (2, 3, 1); INSERT 35301 1 playpen=> select a, count(*), avg(n), sum(n) from tablem group by a; a|count|avg|sum -+-+---+--- 1|5| 2| 11 2|4| 3| 14 (2 rows) ... now suppose I want to have the number of distictive b's in the results as well. I try: playpen=> select a, count(m.oid), avg(m.n), sum(m.n), (select count(mm.oid) from tablem mm where m.a = mm.a group by mm.b ) from tablem m group by a; ERROR: More than one tuple returned by a subselect used as an expression. playpen=> ... even though the subselect should only return one tuple.
Re: [SQL] counting distinct values
Tom Lane wrote: > > Joseph Shraibman <[EMAIL PROTECTED]> writes: > >>>> Using the example from > >>>> http://www.postgresql.org/docs/aw_pgsql_book/node59.html, what would I > >>>> do if I wanted to know the number of different cities where I had a > >>>> friend in each state? select count(city) group by state; would not work > >>>> because if you had two friends in the same city it would be counted > >>>> twice. > > Er, what's wrong with select count(distinct city) group by state? I thought I tried that, but it didn't work the first time. Guess I mistyped something. Sorry. > > > ... now suppose I want to have the number of distictive b's in the > > results as well. I try: > > > playpen=> select a, count(m.oid), avg(m.n), sum(m.n), (select > > count(mm.oid) from tablem mm where m.a = mm.a group by mm.b ) from > > tablem m group by a; > > ERROR: More than one tuple returned by a subselect used as an > > expression. > > playpen=> > > > ... even though the subselect should only return one tuple. > > Not unless there's only one b value for any one a value --- otherwise > the sub-select will return one row per b group. The error message looks > correct to me. > OK I thought that group by was eleminating duplicates which would then be counted. That evolved from q simple select(*) ... group by that worked. Don't know where I screwed it up.
[SQL] BETWEEN
Why is BETWEEN inclusive? I had assumed that it was like the english between, which is exclusive. playpen=# create table numbs ( a int); CREATE playpen=# insert into numbs values(1); INSERT 35913 1 playpen=# insert into numbs values(2); INSERT 35914 1 playpen=# insert into numbs values(3); INSERT 35915 1 playpen=# insert into numbs values(4); INSERT 35916 1 playpen=# insert into numbs values(5); INSERT 35917 1 playpen=# insert into numbs values(6); INSERT 35918 1 playpen=# insert into numbs values(7); INSERT 35919 1 playpen=# select * from numbs where a between 3 and 6; a --- 3 4 5 6 (4 rows)
