[SQL] how do i provide array parameters for my functions in php
i have just finished creating a function that has an array of integers as its parameter. what i would like to know is how do i declare/execute my function in php. i tried several syntax e.g. $arrvalue - an array of integers $strquery = "select functionname($arrvalue)"; $strquery = "select functionname($arrvalue[])"; what is the right syntax TIA joseph ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
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] user defined function question
how do i return a resultset from a user defined function. pls show a simple structure ---(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]
im new in postgresql (actually came from SQL Server) and i was trying a script like this insert into table1(field1,field2) values (select field1, field2 from table 2); i dont know if this is possible (inserting a set of entries via resultset from a select stmt in one command). If anyone has any answers, or workarounds pls do email me Thanks ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[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] variance aggregate function incorrect? Reference Materials regcreate aggregate
hi i needed the variance function ... i dont know if i introduced the wrong parameters or maybe this variance is not the variance that im looking for but it doesnt provide the right results variance=(nEx^2 - (Ex)^2)/(n(n-1)) my sql statement select variance(answer) from tbl_answer (where answer is of type integer) because of this i need to create my own variance aggregate (unless someone enlightens me on whats happening with the variance aggregate) so i would like to know if theres anyone who knows a good create aggregate tutorial (with samples) ... all i got from google so far is the reference from ninthwonder.com TIA ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] assign count() result to a declared variable in plpgsql
i want to put my count() result in a plpgsql declared integer variable declare f_count_var integer; begin select into f_count_var count(empno) from employee end; tried this one but it doesnt work ---(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] transaction in plpgsql
how can i implement transactions inside my plpgsql functions ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] export plpgsql function to file
how can i export my postgresql function to a file? ---(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 do i import my sql query result to a file
how do i import results of my select query to a file thanks ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] how do i import my sql query result to a file
thanks for the tips !!! actually i used \g . Again thanks On Thu, 2002-07-18 at 20:34, Ludwig Lim wrote: > > --- Joseph Syjuco <[EMAIL PROTECTED]> wrote: > > how do i import results of my select query to a file > > thanks > > > > in the psql command prompt type > \o > > and then type your select query. > The result will be dumped into > > ludwig. > > __ > Do You Yahoo!? > Yahoo! Autos - Get free new car price quotes > http://autos.yahoo.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 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] determine if a table exists
how do i determine if a table exists using select statement i want to find out if it exists if not ill create it if yes then ill do some editing with it TIA ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] select case problem
i have a table with the fields empno (not null) and division_no (null allowed) what i wanted to do is to do a select case statement such that when division_no is null itll output 'No division' if its not null itll output 'with division' unfortunately this statement doesnt work ... i need help on the correct syntax select case division_no when null then 'no division' else 'with division' end from employee; ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] getting the current date
how can i get the current date (without the time part) in sql. I tried doing a select now() but it also gives me the time part TIA joseph ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[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] Group By Error Text
Hi with the following query: select jobno, count(jobno) from drawing_register; I get the following error: ERROR: Attribute drawing_register.jobno must be GROUPed or used in an aggregate function Is this correct? Getting rid of the error is easy: select jobno, count(jobno) from drawing_register group by jobno; I believe that jobno is being used in an aggregate function. Should a change be made to the error text? Regards Joe Healy ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[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] Selecting "sample" data from large tables.
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
I have a table with a decent number of rows (let's say for example a
billion rows). I am trying to construct a graph that displays the
distribution of that data. However, I don't want to read in the
complete data set (as reading a billion rows would take a while). Can
anyone thing of a way to do this is postgresql? I've been looking
online and most of the stuff I've found has been for other databases.
As far as I can tell ANSI SQL doesn't provide for this scenario.
I could potentially write a function to do this, however I'd prefer
not to. But if that's what I'm going to be stuck doing I'd like to
know earlier then later. Here's the description of the table:
create table score
{
pageId Integer NOT NULL,
ruleId, Integer NOT NULL
score Double precision NULL,
rowAddedDate BigInt NULL,
primary key (pageId, ruleId)
};
I also have an index on row added date, which is just the number of
millis since the epoc (Jan 1, 1970 or so [java style timestamps]).
I'd be willing to accept that the row added date values are random
enough to represent random.
Thanks in advance,
-- Joe T.
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.2.4 (GNU/Linux)
iD8DBQFAv2Bqs/P36Z9SDAARAkmLAJ9dDB0sqACgFrxH8NukFUsizXz5zgCgt9IT
/wh3ryz4WQzc5qQY2cAZtVE=
=5dg+
-END PGP SIGNATURE-
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[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] ERROR: function expression in FROM may not refer to other relations of same query level
I've seen this asked in the archives, but there was never any answer. Supposed I have this table: create temp table tempa (ids int[]); insert into tempa SELECT ARRAY[1 , 2, 3]; Now how do I get output from that? None of these work: (xunnest is my version of unnest since I'm using 8.2.x) select * FROM xunnest(tempa.ids); SELECT * FROM (select * FROM xunnest(tempa.ids) AS aa) AS ab, tempa; SELECT * FROM ( select * FROM xunnest(ta.ids) AS aa) AS ab, tempa ta; SELECT * FROM tempa ta, ( select * FROM xunnest(ta.ids) AS aa) AS ab; -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] How do I search a two dimensional array?
I can search a one dimensional array with SELECT value = ANY(array), but how do I search two dimensional array for a particular row? ANY seems to flatten out a two dimensional array. [local]:playpen=> select 2 = any (ARRAY[[1,7],[4,2]]); ?column? -- t (1 row) Time: 52.451 ms [local]:playpen=> select ROW(2,7) = any (ARRAY[[1,7],[4,2]]); ERROR: operator does not exist: record = integer LINE 1: select ROW(2,7) = any (ARRAY[[1,7],[4,2]]); ^ HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts. [local]:playpen=> select ROW(4,2) = any (ARRAY[[1,7],[4,2]]); ERROR: operator does not exist: record = integer LINE 1: select ROW(4,2) = any (ARRAY[[1,7],[4,2]]); ^ HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts. [local]:playpen=> [local]:playpen=> [local]:playpen=> select ARRAY[2,7] = ANY( ARRAY[[1,7],[4,2],[6,8]]); ERROR: operator does not exist: integer[] = integer LINE 1: select ARRAY[2,7] = ANY( ARRAY[[1,7],[4,2],[6,8]]); ^ HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts. [local]:playpen=> select ARRAY[1,7] = any ( ARRAY[[1,7],[4,2],[6,8]]); ERROR: operator does not exist: integer[] = integer LINE 1: select ARRAY[1,7] = any ( ARRAY[[1,7],[4,2],[6,8]]); ^ HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts. [local]:playpen=> -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[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)
[SQL] Referencing named attribute in where clause doesn't work with 7.1.2?
Hi, this is my first post to this list so please... I have problems getting this query to work, any ideas? select article.title_text_key, (select on_text.text_value from on_text where on_text.text_key = title_text_key AND NOT title_text_key is NULL AND on_text.lang_id = (select code.id from code, code_group where code.code_group_id = code_group.id AND code_group.description = 'lang' AND code.code_key = 'lang.NO' ) ) as title_text_value from article where NOT title_text_value is NULL; ERROR: Attribute 'title_text_value' not found Issuing the same query without the where-clause does work tho, but return tupples with null in them which I don't want. -- Andreas Joseph Krogh <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Referencing named attribute in where clause doesn't workwith 7.1.2?
Thomas Good wrote: > > On Wed, 8 Aug 2001, Andreas Joseph Krogh wrote: > > > Hi, this is my first post to this list so please... > > I have problems getting this query to work, any ideas? > > > > select article.title_text_key, > > (select on_text.text_value from on_text where > > on_text.text_key = title_text_key > > AND NOT title_text_key is NULL > > AND on_text.lang_id = (select code.id from code, code_group where > >code.code_group_id = code_group.id > > AND code_group.description = 'lang' AND code.code_key = 'lang.NO' > > ) > > ) > > as title_text_value from article where NOT title_text_value is NULL; > > > > ERROR: Attribute 'title_text_value' not found > > Hallo Andreas, > > Relocating your AS clause to the topmost string? BTW, table aliasing > would make for less typing. > > Best of luck! I don't get it, what do you mean by "Relocating your AS clause to the topmost string"? -- Andreas Joseph Krogh <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [SQL] RE: Referencing named attribute in where clause doesn't work with7.1.2?
Jeff Eckermann wrote: > > The WHERE clause is evaluated before your SELECT list is determined, so the > aliased value cannot be used. > You can put further NOT NULL tests into the subqueries to make sure that > null values are not returned. > Question: why not just join the tables explicitly? :-) Because I'm not too familiar with joins. > The more usual SQL > approach would be something like: > > SELECT article.title_text_key, on_text.text_value AS title_text_value > FROM article INNER JOIN (on_text INNER JOIN (code INNER JOIN code_group ON > code.code_group_id = code.id) ON on_text.lang_id = code.id) ON > article.title_text_key = on_text.text_key > WHERE on_text.text_value IS NOT NULL; > > or whatever other tests you want. In this case, you can easily reference > the fields by name. The problem with the query above is that it doesn't include my "code.code_key='lang.NO'" test. I rephrased the query as follows: SELECT article.title_text_key, on_text.text_value AS title_text_value FROM article INNER JOIN (on_text INNER JOIN (code INNER JOIN code_group ON code.code_group_id = code_group.id) ON on_text.lang_id = code.id AND code_group.description = 'lang' AND code.code_key = 'lang.NO') ON article.title_text_key = on_text.text_key WHERE on_text.text_value IS NOT NULL; And now it works! Thank you for helping me out. -- Andreas Joseph Krogh <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Trigger Problem
Mohammad Faisal <[EMAIL PROTECTED]> said: > hey all > > > I have created a function that is used in a trigger. > > -- > -- > > CREATE TRIGGER tr_insert_on_a AFTER INSERT OR UPDATE > ON A > FOR EACH ROW EXECUTE PROCEDURE fn_insert_on_a(); > > -- > -- > > CREATE function fn_insert_on_a() RETURNS OPAQUE AS > ' > BEGIN > INSERT INTO B name, a_ref VALUES > (NEW.name,NEW.id); > RETURN NEW; > END; > > ' LANGUAGE 'plpgsql'; > > > I am getting following error. > ERROR : > > Unrecognized language specified in a CREATE FUNCTION: > plpgsql. Recognized languages are sql,c,internal and > the created procedure languages. > > NOTE: > > I have studied in documenatation that only plpgsql > functions are written to be used in triggers. You need to issue: $ createlang plpgsql to create the plpgsql language on your database. -- Andreas Joseph Krogh <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] graphical interface - admin
On Friday 28 June 2002 17:30, Josh Berkus wrote: > David, > > > As for what I first said in this mail, I'll launch the development of > > a Kde > > application (I think called KPostgreSQL...), which will have to fit > > the > > following : > > Listen, the PGAdminII "team" (i.e. David Page) is planning to port > PGAdmin to a more OS-independant platform, such as C++ or Java. > Instead of striking out on your own, how about you get together with > them? > > Or, in the short term, you could do the community a huge service just > by ressurecting KPSQL, which died with KDE 1.1. It just needs a KDE > expert to dust it off and make it compatible with KDE 3.0. > > Just my thoughts. I'd just really hate to see another 50% complete > PostgreSQL interface -- we have too many, already. > > -Josh Berkus Take a look at TOra - Toolkit For Oracle: http://www.globecom.se/tora/ It has excellent PostgreSQL support. -- Andreas Joseph Krogh (Senior Software Developer) <[EMAIL PROTECTED]> "Everything should be made as simple as possible, but not simpler" - Albert Einstein ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] graphical interface - admin
On Monday 01 July 2002 13:29, you wrote: > On Mon, 1 Jul 2002, Andreas Joseph Krogh wrote: > > Take a look at TOra - Toolkit For Oracle: http://www.globecom.se/tora/ > > > > It has excellent PostgreSQL support. > > I tried 1.2.4 but didn't found how to configure tora to work > with postgresql. I have a version here which works for me: http://www.officenet.no/~andreak/tora-alpha-1.3.4-1static.i686.rpm If you get a core-dump, try temporarily to remove your $HOME/.qt dir. I have not gotten the newer version(1.3.5.1) to work with PostgreSQL(I only get Oracle and MySQL in the combo-box even though its staticly linked with pgsql). -- Andreas Joseph Krogh (Senior Software Developer) <[EMAIL PROTECTED]> "Everything should be made as simple as possible, but not simpler" - Albert Einstein ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] graphical interface - admin
On Monday 01 July 2002 15:00, Oleg Bartunov wrote: > On Mon, 1 Jul 2002, Andreas Joseph Krogh wrote: > > On Monday 01 July 2002 13:29, you wrote: > > > On Mon, 1 Jul 2002, Andreas Joseph Krogh wrote: > > > > Take a look at TOra - Toolkit For Oracle: > > > > http://www.globecom.se/tora/ > > > > > > > > It has excellent PostgreSQL support. > > > > > > I tried 1.2.4 but didn't found how to configure tora to work > > > with postgresql. > > > > I have a version here which works for me: > > http://www.officenet.no/~andreak/tora-alpha-1.3.4-1static.i686.rpm > > > > If you get a core-dump, try temporarily to remove your $HOME/.qt dir. > > > > I have not gotten the newer version(1.3.5.1) to work with PostgreSQL(I > > only get Oracle and MySQL in the combo-box even though its staticly > > linked with pgsql). > > How did you configure sources ? There is no --with-pgsql option I didn't:-) I downloaded the precompiled static binary. -- Andreas Joseph Krogh (Senior Software Developer) <[EMAIL PROTECTED]> "Everything should be made as simple as possible, but not simpler" - Albert Einstein ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Editor for pgsql
On Tuesday 23 July 2002 07:45, Roberto Mello wrote:
> On Mon, Jul 22, 2002 at 05:41:39PM -0700, Josh Berkus wrote:
> > I tried to install Tora, but the build blew up since I don't have Oracle
> > installed. Any tips?
>
> I just use the Debian packages (/me hugs Debian).
>
> AFAIK, all you have to do is compile with the appropriate flags so it
> doesn't try to build Oracle support (you need a full Oracle installation),
> and also MySQL support.
>
> You also need to have the PostgreSQL loadable Qt 3 module installed before
> compiling, plus Qt 3 headers and such.
>
> You could try downloading the binary Debian packages from
> packages.debian.org ("unstable" distribution) and unpacking them (Debian
> packages are just "ar" packages with extra headers).
I just compiled the latest version(tora-1.3.6.1) right now(on Mandrake-8.1
with KDE-3.0.2 ant qt-3.0.4) with the following options to configure:
./configure --without-oracle --without-kde
make
su -c "make install"
This compiles and installes just fine to /usr/local/bin with PostgreSQL
support.
--
Andreas Joseph Krogh (Senior Software Developer) <[EMAIL PROTECTED]>
- There are 10 kinds of people in the world, those that can do binary
arithmetic and those that can't.
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Stripping white-space in SELECT statments
On Thursday 19 September 2002 13:41, Thorbjörn Eriksson wrote: > By "our system" I mean the software that uses the database. It is a quit > old software written in C that has been ported a couple of times to > different *NIX platforms using different DBMS's. It uses in-house developed > functions that, depending on parameters, creates a variety of SQL > statements that in the end gets executed by PQexec (from libpq i guess). [snip] > > > artnrgrpmtrln_1<='201901 ÿ' This looks like to me that your C software doesn't '\0' terminate its strings proparly and some random byte gets in the query. -- Andreas Joseph Krogh <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] 7.3 schemas
Hi! I've just started to look at 7.3, and have created a schema with the "CREATE SCHEMA" command. How do I use/connect to theese schemas? I also want to use the schemas form JDBC, how is the connect-URL sopposed to be? Any links to docs on the new schema support are appreciated. -- Andreas Joseph Krogh <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] Problems with to_char(created, 'WW')
Hi. I have the following schema in PostgreSQL-7.2.2: CREATE TABLE session ( session_id varchar(256) NOT NULL PRIMARY KEY, created timestamp DEFAULT 'now' NOT NULL, last_accessed timestamp NOT NULL, destroyed timestamp NOT NULL, username varchar -- Allow sessions from not logged in users ); Now I run this query to extract the week numbers: janerik=# select created, to_char(created, 'WW') as week from session WHERE username IS NULL ORDER BY week; created | week +-- 2002-09-24 11:23:21.206+02 | 39 2002-09-24 18:19:06.304+02 | 39 2002-09-25 10:50:27.139+02 | 39 2002-09-30 12:32:06.898+02 | 39 2002-10-01 16:26:41.122+02 | 40 2002-10-04 13:47:02.922+02 | 40 2002-10-04 21:28:13.409+02 | 40 2002-10-04 17:35:50.954+02 | 40 2002-10-04 23:31:27.566+02 | 40 2002-10-04 23:34:18.286+02 | 40 2002-10-07 13:48:14.275+02 | 40 2002-10-07 13:50:04.352+02 | 40 2002-10-07 14:10:01.441+02 | 40 2002-10-11 12:57:53.458+02 | 41 2002-10-11 13:24:49.124+02 | 41 2002-10-13 16:26:52.546+02 | 41 2002-10-14 23:50:51.131+02 | 41 2002-10-15 14:54:12.341+02 | 42 2002-10-15 15:09:36.84+02 | 42 2002-10-15 15:21:26.59+02 | 42 2002-10-20 12:14:05.203+02 | 42 2002-10-20 20:19:44.309+02 | 42 2002-10-21 14:23:31.425+02 | 42 2002-10-22 12:12:31.63+02 | 43 2002-10-23 14:00:18.478+02 | 43 (25 rows) Now - my question is, why is monday 21. in week 42, but tuesday 22. in week 43? -- Andreas Joseph Krogh <[EMAIL PROTECTED]> - There are 10 kinds of people in the world, those that can do binary arithmetic and those that can't. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] importing a 7.2 db with contrib/tsearch to 7.3
If this is the wrong list, please redirect me to the correct one. I have a PostgreSQL-7.2 installation with several databases in it which uses the contrib/tsearch module. I've done a (on 7.2): pg_dumpall and (on 7.3): /usr/local/pgsql/bin/psql -d template1 -f prod2-pg7.2.dmp I get a bunch of theese errors in the log-file: --- psql:prod2-pg7.2.dmp:89: NOTICE: TypeCreate: changing argument type of function qtxt_in from OPAQUE to CSTRING psql:prod2-pg7.2.dmp:89: NOTICE: TypeCreate: changing argument type of function qtxt_out from OPAQUE to query_txt psql:prod2-pg7.2.dmp:89: NOTICE: TypeCreate: changing return type of function qtxt_in from OPAQUE to query_txt psql:prod2-pg7.2.dmp:89: NOTICE: TypeCreate: changing return type of function qtxt_out from OPAQUE to CSTRING CREATE TYPE CREATE FUNCTION psql:prod2-pg7.2.dmp:105: NOTICE: TypeCreate: changing argument type of function mqtxt_in from OPAQUE to CSTRING psql:prod2-pg7.2.dmp:105: ERROR: TypeCreate: function qtxt_out(mquery_txt) does not exist CREATE FUNCTION psql:prod2-pg7.2.dmp:121: ERROR: Type "mquery_txt" does not exist CREATE FUNCTION psql:prod2-pg7.2.dmp:137: ERROR: Type "mquery_txt" does not exist CREATE FUNCTION psql:prod2-pg7.2.dmp:153: ERROR: Type "mquery_txt" does not exist CREATE OPERATOR CREATE OPERATOR psql:prod2-pg7.2.dmp:192: ERROR: Type "mquery_txt" does not exist psql:prod2-pg7.2.dmp:205: ERROR: Type "mquery_txt" does not exist .. .. psql:prod2-pg7.2.dmp:2539: ERROR: data type txtidx has no default operator class for access method "gist" You must specify an operator class for the index or define a default operator class for the data type .. .. psql:prod2-pg7.2.dmp:4419: ERROR: execqtxt: must be owner psql:prod2-pg7.2.dmp:4427: ERROR: Type "mquery_txt" does not exist psql:prod2-pg7.2.dmp:4435: ERROR: rexecqtxt: must be owner psql:prod2-pg7.2.dmp:4443: ERROR: Type "mquery_txt" does not exist --- The database seems to work just fine, and one of the tables which uses the txtidx datatype looks like this in 7.2 and 7.3 respectively: e4u=> select version(); version PostgreSQL 7.2.2 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2 (Mandrake Linux 9.0 3.2-1mdk) (1 row) e4u=> \d on_article_searchable Table "on_article_searchable" Column | Type | Modifiers -+-+--- id | integer | not null lang_id | integer | not null content | txtidx | Indexes: searchable_lang_idx, t_idx Primary key: on_article_searchable_pkey Triggers: RI_ConstraintTrigger_18817, RI_ConstraintTrigger_18823 e4u=> select version(); version - PostgreSQL 7.3rc1 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2 (Mandrake Linux 9.0 3.2-1mdk) (1 row) e4u=> \d on_article_searchable Table "public.on_article_searchable" Column | Type | Modifiers -+-+--- id | integer | not null lang_id | integer | not null content | txtidx | Indexes: on_article_searchable_pkey primary key btree (id, lang_id), searchable_lang_idx btree (lang_id) Triggers: RI_ConstraintTrigger_41091, RI_ConstraintTrigger_41094 Notice, the t_idx index is missing on the 7.3 table. Does that mean I have to manually "install" tsearch on each database? When I try to create the index t_idx on the table, the following error occures: e4u=> CREATE INDEX t_idx ON on_article_searchable USING gist (content); ERROR: data type txtidx has no default operator class for access method "gist" You must specify an operator class for the index or define a default operator class for the data type Any help appreciated. -- Andreas Joseph Krogh <[EMAIL PROTECTED]> - Only two things are infinite, the universe and human stupidity, and I'm not sure about the former. Albert Einstein ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Need Postgresql Help
On Monday 02 December 2002 09:44, Atul wrote: > Respected sir, > Atul Pedgaonkar here from India. I am using postgresql7.2 as > backend and for front-end, perl5.6.1. I need some help regarding > postgresql-stored procedure. The problem is like this > > 1)How to create stored procedure in postgresql? > 2)How can i use it from perl script with parameters. ? > 3)How to return resultset from that Stored Procedure ? You need 7.3 to do 3). Infor on stored procedures: $INSTALLDIR/doc/html/plpgsql.html -- Andreas Joseph Krogh <[EMAIL PROTECTED]> : What does this "kernel" program do? - It does automated remote popcorn popping using XML-RPC to communicate bidirectionally with TCP/IP enabled microwave ovens. (Slashdot reply) ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: Upgrade question - was Re: [SQL] join and dynamic view
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Tuesday 17 December 2002 16:09, Christoph Haller wrote: > > That explains it - the server I'm developing on is quite old - I > > didn't > > > realise how old. I'm about to do an upgrade from 7.1.3 to 7.2.1-5 > > over > > > christmas in fact. > > > > Will I need to dump/restore the database for this upgrade? As allways when version-upgrading(and not patch-level), you need to dump/restore as the binary on-disk format changes. - -- Andreas Joseph Krogh <[EMAIL PROTECTED]> The difference between insanity and genius is measured by success -BEGIN PGP SIGNATURE- Version: GnuPG v1.0.7 (GNU/Linux) iD8DBQE9/0GGUopImDh2gfQRAgJJAJ46UWmX4OBWogSHQOGOsO5w1+ZEDQCfcTFy XpXZyDZfRZAAHs1i7lixKQQ= =yI3q -END PGP SIGNATURE- ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] index on to_char(created, 'YYYY') doesn't work
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 The following does not work: create index session_u_idx on session (to_char(created, '')); ERROR: parser: parse error at or near "''" at character 57 Can I make a function to do this and index using the result of that funtion? Do anyone have an example of such a function? - -- Andreas Joseph Krogh <[EMAIL PROTECTED]> There will always be someone who agrees with you but is, inexplicably, a moron. gpg public_key: http://dev.officenet.no/~andreak/public_key.asc -BEGIN PGP SIGNATURE- Version: GnuPG v1.0.7 (GNU/Linux) iD8DBQE+JUfhUopImDh2gfQRAme7AJ4jDB+e97rvEicGrxBniD1ddQ1gZgCfbbGl azbrt7/+xGJUuLSQC7fF+vQ= =3pKN -END PGP SIGNATURE- ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] index on to_char(created, 'YYYY') doesn't work
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Wednesday 15 January 2003 11:37, you wrote: > The following does not work: > > create index session_u_idx on session (to_char(created, '')); > ERROR: parser: parse error at or near "''" at character 57 > > Can I make a function to do this and index using the result of that > funtion? Do anyone have an example of such a function? I tried the following function: - - create function drus (timestamp) returns varchar AS' DECLARE str_created VARCHAR; created ALIAS FOR $1; BEGIN str_created:= to_char(created, ''''); RETURN str_created; END; ' LANGUAGE 'plpgsql'; create index session_u_idx on session (drus(created)); - - But it failes with: ERROR: DefineIndex: index function must be marked isImmutable Now the question is how do I mark an index function isImmutable? - -- Andreas Joseph Krogh <[EMAIL PROTECTED]> There will always be someone who agrees with you but is, inexplicably, a moron. gpg public_key: http://dev.officenet.no/~andreak/public_key.asc -BEGIN PGP SIGNATURE- Version: GnuPG v1.0.7 (GNU/Linux) iD8DBQE+JUtlUopImDh2gfQRAl1XAKCkEDKxRDys/Di1gLLRDx6h0TGiPwCeI4FN DNdajyaQTd27f8MeaWZ+xUE= =T3we -END PGP SIGNATURE- ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] index on to_char(created, 'YYYY') doesn't work
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Wednesday 15 January 2003 16:12, you wrote: > On Wed, 15 Jan 2003, Andreas Joseph Krogh wrote: > > -BEGIN PGP SIGNED MESSAGE- > > Hash: SHA1 > > > > On Wednesday 15 January 2003 11:37, you wrote: > > > The following does not work: > > > > > > create index session_u_idx on session (to_char(created, '')); > > > ERROR: parser: parse error at or near "''" at character 57 > > > > > > Can I make a function to do this and index using the result of that > > > funtion? Do anyone have an example of such a function? > > > > I tried the following function: > > - - > > create function drus (timestamp) returns varchar AS' > > DECLARE > > str_created VARCHAR; > > created ALIAS FOR $1; > > BEGIN > > str_created:= to_char(created, ''''); > > RETURN str_created; > > END; > > ' LANGUAGE 'plpgsql'; > > add > WITH (iscachable) Thank you, not _that_ works:-) But now this doesn't work: create index session_u_idx on session (drus(created), username); Can't I have multicolumn-indexes with functions? Any idea how to rewrite that so it works? Here is my session table: CREATE TABLE session ( session_id varchar(256) NOT NULL PRIMARY KEY, created timestamp DEFAULT 'now' NOT NULL, last_accessed timestamp NOT NULL, destroyed timestamp NOT NULL, username varchar -- Allow sessions from not logged in users ); Here is my query I wish to optimize using indexes: SELECT to_char(created, 'IW') as week, count(session_id) from session WHERE username IS NULL and to_char(created, '') = '2002' group by week ORDER BY week; Any hints on optimizing this query, index-usage etc? - -- Andreas Joseph Krogh <[EMAIL PROTECTED]> There will always be someone who agrees with you but is, inexplicably, a moron. gpg public_key: http://dev.officenet.no/~andreak/public_key.asc -BEGIN PGP SIGNATURE- Version: GnuPG v1.0.7 (GNU/Linux) iD8DBQE+JU8hUopImDh2gfQRAuA5AKCXyqCZk92d6oCgyJ/Auf8c4xkSaQCgr4Lq /+r2WSydbYWXNomMvbmt2E8= =N6NQ -END PGP SIGNATURE- ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] index on to_char(created, 'YYYY') doesn't work
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Wednesday 15 January 2003 18:55, Achilleus Mantzios wrote: > On Wed, 15 Jan 2003, Andreas Joseph Krogh wrote: > > -BEGIN PGP SIGNED MESSAGE- > > Hash: SHA1 > > > > On Wednesday 15 January 2003 16:12, you wrote: > > > On Wed, 15 Jan 2003, Andreas Joseph Krogh wrote: > > > > -BEGIN PGP SIGNED MESSAGE- > > > > Hash: SHA1 > > > > > > > > On Wednesday 15 January 2003 11:37, you wrote: > > > > > The following does not work: > > > > > > > > > > create index session_u_idx on session (to_char(created, '')); > > > > > ERROR: parser: parse error at or near "''" at character 57 > > > > > > > > > > Can I make a function to do this and index using the result of that > > > > > funtion? Do anyone have an example of such a function? > > > > > > > > I tried the following function: > > > > - - > > > > create function drus (timestamp) returns varchar AS' > > > > DECLARE > > > > str_created VARCHAR; > > > > created ALIAS FOR $1; > > > > BEGIN > > > > str_created:= to_char(created, ''''); > > > > RETURN str_created; > > > > END; > > > > ' LANGUAGE 'plpgsql'; > > > > > > add > > > WITH (iscachable) > > > > Thank you, not _that_ works:-) > > But now this doesn't work: > > create index session_u_idx on session (drus(created), username); > > Functinal indexes are single column indexes. > > Why dont you change your function to: > > create function drus (timestamp,varchar) returns varchar A > > and return the concatenation of to_char(created, '''')||$2 > > and then create the index as usual (passing the date and the username > as params to your function) OK, thank you. Let me explain what I want to do: I have the following schema for gathering statistics from the web: CREATE TABLE session ( session_id varchar(256) NOT NULL PRIMARY KEY, created timestamp DEFAULT 'now' NOT NULL, last_accessed timestamp NOT NULL, destroyed timestamp NOT NULL, username varchar -- Allow sessions from not logged in users ); create or replace function drus (timestamp) returns varchar AS' DECLARE str_created VARCHAR; created ALIAS FOR $1; BEGIN str_created:= to_char(created, ''''); RETURN str_created; END; ' LANGUAGE 'plpgsql' WITH (iscachable); create index session_u_idx on session (drus(created)) where username is null; Now I want to get statistics for number of hits pr. week where users are not lnogged in(username IS NULL) for the year 2002: select to_char(created, 'IW') as week, count(session_id) from session WHERE username IS NULL and drus(created) = '2002' group by week ORDER BY week; week | count - --+--- 01 | 6321 18 |74 19 | 12153 20 | 17125 21 | 22157 22 | 25316 23 | 24265 24 | 26234 25 | 28583 26 | 29156 27 | 28335 28 | 23587 29 | 23203 This table is quite large(900 000 rows) and the query takes several minutes to run, which makes the browser timeout. Do I have a design-issue here, should I rather batch-generate the stats in its own table so I don't have to process all the data(900 000 rows) each time. Is there any way to optimize/rewrite this query? Is the use of to_char on the timestamp wrong, should I use another comparation method for getting the year 2002? - -- Andreas Joseph Krogh <[EMAIL PROTECTED]> There will always be someone who agrees with you but is, inexplicably, a moron. gpg public_key: http://dev.officenet.no/~andreak/public_key.asc -BEGIN PGP SIGNATURE- Version: GnuPG v1.0.7 (GNU/Linux) iD8DBQE+JXiPUopImDh2gfQRAlf/AJ9xlcUDqa7NcXghtse8PAqQxkf1lACdEGxH vBXYxoFZnS6J35iQGw+14wE= =xCVY -END PGP SIGNATURE- ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] index on to_char(created, 'YYYY') doesn't work
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Wednesday 15 January 2003 20:02, you wrote: > You didnt try it!! > > Change your to_char(created, '''')||$2 to > to_char(created, '''')||(coalesce($2,'''') > (provided there is no user named mister '' :) > > then perform your query like: > > select to_char(created, 'IW') as week, count(session_id) from session > WHERE drus(created,username) = '2002' group by week ORDER BY > week; > > do a explain analyze to see index and performance issues. I didn't try it because I don't have a problem with the optimizer utilizing the index anymore. As you can se in the attachment the index is used. Quoting Tom Lane: "he real problem is very likely that the query selects such a large fraction of the table rows that the index isn't buying you anything." nbeweb=> select count(*) from session; count - 899691 (1 row) nbeweb=> select count(*) from session where username IS NULL; count - 898377 (1 row) The output of EXPLAIN and EXPLAIN ANALYZE is in the attachment. Can anyone explain to me how to reed the output from ANALYZE. It seems most of the time is spent sorting and grouping. Are there any ways to optimize this? - -- Andreas Joseph Krogh <[EMAIL PROTECTED]> There will always be someone who agrees with you but is, inexplicably, a moron. gpg public_key: http://dev.officenet.no/~andreak/public_key.asc -BEGIN PGP SIGNATURE- Version: GnuPG v1.0.7 (GNU/Linux) iD8DBQE+JZx+UopImDh2gfQRAjfJAKCv4uXE2PhtmWfCvm/6pRkumfM8KACgmeDF AX9HeKVu9SErXxpaUh9ys4A= =sPIN -END PGP SIGNATURE- nbeweb=> EXPLAIN select to_char(created, 'IW') as week, count(session_id) from session WHERE username IS NULL and drus(created) = '2002' group by week ORDER BY week; NOTICE: QUERY PLAN: Aggregate (cost=92.62..92.74 rows=2 width=154) -> Group (cost=92.62..92.68 rows=22 width=154) -> Sort (cost=92.62..92.62 rows=22 width=154) -> Index Scan using session_u_idx on session (cost=0.00..92.12 rows=22 width=154) EXPLAIN nbeweb=> EXPLAIN ANALYZE select to_char(created, 'IW') as week, count(session_id) from session WHERE username IS NULL and drus(created) = '2002' group by week ORDER BY week; NOTICE: QUERY PLAN: Aggregate (cost=92.62..92.74 rows=2 width=154) (actual time=764457.99..837660.30 rows=36 loops=1) -> Group (cost=92.62..92.68 rows=22 width=154) (actual time=763934.48..830755.34 rows=846053 loops=1) -> Sort (cost=92.62..92.62 rows=22 width=154) (actual time=763934.45..798906.16 rows=846053 loops=1) -> Index Scan using session_u_idx on session (cost=0.00..92.12 rows=22 width=154) (actual time=140.71..253768.07 rows=846053 loops=1) Total runtime: 837759.64 msec EXPLAIN nbeweb=> ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Backup of multiple tables
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi. I usually backup my database with pg_backup without the -t option. But now I need to only backup certain tables(say tab1 and tab2), is this possible with pg_dump? I've tried with "pg_dump -t tab1 -t tab2" without success. - -- Andreas Joseph Krogh <[EMAIL PROTECTED]> Managing Director, Senior Software Developer OfficeNet AS - - Writing software is more fun than working. gpg public_key: http://dev.officenet.no/~andreak/public_key.asc -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.1 (GNU/Linux) iD8DBQE/au5eUopImDh2gfQRAsu4AKC0R9WhMMlqbRAPhe+Si+zykxe5bACeLOCm VRBGOqu78we2O9IxbOTlWIc= =5Keu -END PGP SIGNATURE- ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Backup of multiple tables
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Friday 19 September 2003 17:38, Tom Lane wrote: > Andreas Joseph Krogh <[EMAIL PROTECTED]> writes: > > I usually backup my database with pg_backup without the -t option. But > > now I need to only backup certain tables(say tab1 and tab2), is this > > possible with pg_dump? I've tried with "pg_dump -t tab1 -t tab2" without > > success. > > pg_dump can only handle one -t option at a time. It'd make sense to > allow multiple -t options (likewise -n) but no one's got round to > improving the code in that particular direction. I don't think it would > be hard; want to fix it and send in a patch? I've never looked at the code-base of pgsql before, but I'll give it a try. Don't expect anything real soon tho. - -- Andreas Joseph Krogh <[EMAIL PROTECTED]> Managing Director, Senior Software Developer OfficeNet AS - - Writing software is more fun than working. gpg public_key: http://dev.officenet.no/~andreak/public_key.asc -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.2 (GNU/Linux) iD8DBQE/b3jeUopImDh2gfQRAhP3AKCwsviN37+7ZAG4x4hI/ErQEyKmHACfW4on EI900FlO/lDInoDnf9nDEM0= =Xcq/ -END PGP SIGNATURE- ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Inserting data in a table using sub-selects]
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Wednesday 10 March 2004 21:56, Marty Scholes wrote: > INSERT INTO table2 (id, content) ( > SELECT id, coalesce(title, '') || ' ' || coalesce(description, '') > FROM table1 t1); > > If you want to keep them syncrhonized, in other words, rerun the query > over and over again without having to truncate table2 first or deleting > all of the rows, you can: > > INSERT INTO table2 (id, content) ( > SELECT id, coalesce(title, '') || ' ' || coalesce(description, '') > FROM table1 t1) > WHERE id NOT IN ( > SELECT id > FROM table1); > > With Oracle there is a slick way to do a partial outer join that allowed > you to do this without creating a complete list of table1.id in the last > subquery, but I dunno if Pg has an equivalent mechanism. Thanks, works perfectly. - -- Andreas Joseph Krogh <[EMAIL PROTECTED]> Managing Director, Senior Software Developer OfficeNet AS I always do a CVS update before making a patch (unless I forget). gpg public_key: http://dev.officenet.no/~andreak/public_key.asc -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.2 (GNU/Linux) iD8DBQFAT5fCUopImDh2gfQRArmFAJ4qx31Wx31sehCBo7qfjPwwPWvUzACguw9t H8mWrl+TsdYIhmQ+FDu41t8= =qj1H -END PGP SIGNATURE- ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Problem with LATIN1 characters from Perl-DBI
Hi.
I have a database created with -E LATIN1. Inserting norwegian characters lik
'ø' works perfectly from JDBC, but from Perl, it stores the word 'søker' as
'søker'(UNICODE).
perl --version:
This is perl, v5.8.3 built for i386-linux-thread-multi
A Mandrake-10 Linux system.
I first had the problem printing out LATIN1 chars to stdout too, but solved
that by using the pragma
use encoding 'ISO-8859-1';
I've tried:
$dbh->do("set CLIENT_ENCODING TO 'ISO-8859-1'")
or die("Couldn't set encoding to ISO-8859-1");
but that didn't work.
Any hints anyone?
--
Andreas Joseph Krogh <[EMAIL PROTECTED]>
Senior Software Developer / Manager
gpg public_key: http://dev.officenet.no/~andreak/public_key.asc
+-+
OfficeNet AS| Two tomatoes in a fridge. One tomato says |
Hoffsveien 17 | to the other, "It's cold in here, isn't it?"|
PO. Box 425 Skøyen | The other tomato says, "F**king hell, |
0213 Oslo | a talking tomato!" |
NORWAY | |
Phone : +47 22 13 01 00 | |
Direct: +47 22 13 10 03 | |
Mobile: +47 909 56 963 | |
+-+
pgpl5sdpAFxjq.pgp
Description: PGP signature
Re: [SQL] Problem with LATIN1 characters from Perl-DBI
On Tuesday 07 September 2004 14:06, you wrote:
> Hi.
> I have a database created with -E LATIN1. Inserting norwegian characters
> lik 'ø' works perfectly from JDBC, but from Perl, it stores the word
> 'søker' as 'søker'(UNICODE).
>
> perl --version:
> This is perl, v5.8.3 built for i386-linux-thread-multi
> A Mandrake-10 Linux system.
>
> I first had the problem printing out LATIN1 chars to stdout too, but solved
> that by using the pragma
> use encoding 'ISO-8859-1';
>
> I've tried:
> $dbh->do("set CLIENT_ENCODING TO 'ISO-8859-1'")
> or die("Couldn't set encoding to ISO-8859-1");
> but that didn't work.
>
> Any hints anyone?
Replying to my self:
I fixed it by using the following:
use encoding 'ISO-8859-1';
use Unicode::MapUTF8 qw(to_utf8 from_utf8 utf8_supported_charset);
$tmp_text = from_utf8({ -string => $plain_text, -charset => 'ISO-8859-1' });
$retval = $insert_stmt->execute($tmp_text);
The problem was that the contents of $plain_text was obtained by some library
which returned text in utf8. When printing it out to stdout, the 'use
encoding' pragma took care of the conversion, but that didn't work for
inserting the contents of $plain_text into the database. So I must convert it
to latin1 using the from_utf8 subroutine *before* inserting it into the DB.
--
Andreas Joseph Krogh <[EMAIL PROTECTED]>
Senior Software Developer / Manager
gpg public_key: http://dev.officenet.no/~andreak/public_key.asc
+-+
OfficeNet AS| Two tomatoes in a fridge. One tomato says |
Hoffsveien 17 | to the other, "It's cold in here, isn't it?"|
PO. Box 425 Skøyen | The other tomato says, "F**king hell, |
0213 Oslo | a talking tomato!" |
NORWAY | |
Phone : +47 22 13 01 00 | |
Direct: +47 22 13 10 03 | |
Mobile: +47 909 56 963 | |
+-+
pgpjEketBA8Lk.pgp
Description: PGP signature
[SQL] Making NULL entries appear first when ORDER BY ASC
Hi, I have the following table, with the query below to list entries from it where start_date IS NOT NULL: CREATE TABLE onp_crm_activity_log( id serial PRIMARY KEY, start_date timestamp, start_time timestamp, end_time timestamp, title varchar NOT NULL ); SELECT start_date, start_time, end_time, title FROM onp_crm_activity_log WHERE start_date IS NOT NULL ORDER BY start_date ASC, start_time ASC; start_date | start_time | end_time | title -+-+-+--- 2005-02-03 00:00:00 | 2005-02-03 08:00:00 | | Something 2005-02-03 00:00:00 | 2005-02-03 09:00:00 | 2005-02-03 12:00:00 | Something 2005-02-03 00:00:00 | | | Something Now, as you see, touples with NULL in the "start_time"-field appear "after" the others. I would like to make all entries where start_time IS NULL apear *before* all the others. Any idea how to achieve this? -- Andreas Joseph Krogh <[EMAIL PROTECTED]> Senior Software Developer / Manager gpg public_key: http://dev.officenet.no/~andreak/public_key.asc +-+ OfficeNet AS| The most difficult thing in the world is to | Hoffsveien 17 | know how to do a thing and to watch | PO. Box 425 Skøyen | somebody else doing it wrong, without | 0213 Oslo | comment.| NORWAY | | Phone : +47 22 13 01 00 | | Direct: +47 22 13 10 03 | | Mobile: +47 909 56 963 | | +-+ pgpcVCtrEQCSu.pgp Description: PGP signature
Re: [SQL] Making NULL entries appear first when ORDER BY ASC
On Wednesday 16 February 2005 04:47, Bruno Wolff III wrote: > > Now, as you see, touples with NULL in the "start_time"-field appear > > "after" the others. I would like to make all entries where start_time IS > > NULL apear *before* all the others. Any idea how to achieve this? > > SELECT start_date, start_time, end_time, title > FROM onp_crm_activity_log > WHERE start_date IS NOT NULL > ORDER BY start_date ASC, start_time IS NOT NULL ASC, start_time ASC; > > This assumes you want the NULL start times first within a particular > date. Otherwise change the order in the ORDER BY clause. Thanks! This si, IMO, the cleanest solution as it doesn't involve any COALESCE. -- Andreas Joseph Krogh <[EMAIL PROTECTED]> Senior Software Developer / Manager gpg public_key: http://dev.officenet.no/~andreak/public_key.asc +-+ OfficeNet AS| The most difficult thing in the world is to | Hoffsveien 17 | know how to do a thing and to watch | PO. Box 425 Skøyen | somebody else doing it wrong, without | 0213 Oslo | comment.| NORWAY | | Phone : +47 22 13 01 00 | | Direct: +47 22 13 10 03 | | Mobile: +47 909 56 963 | | +-+ pgpFAljpSkpCk.pgp Description: PGP signature
