[SQL] Select very slow...
Table 1 create table person ( doc text primary key, etc city text ); 9 rows create table sales ( doc text, etc . ); 30 rows select p.city,count(*) from sales s, person p where s.doc = p.doc group by p.city; Anyone help-me? ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Select very slow...
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?) 3: EXPLAIN ; That should give you some hints on what to optimize. -- Dave ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [SQL] update table sequence
[EMAIL PROTECTED] (Egbert Ellenkamp) writes:
> select setval('mytable_myrowid_seq',select max(myrowid) from mytable);
That should work if you put in the required parentheses around the
sub-select:
select setval('mytable_myrowid_seq', (select max(myrowid) from mytable));
regards, tom lane
---(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] What do I do with this error?
Folks, Never mind. It turns out that if you call try to feed a NULL to a regular expression (e.g. Client_name ~* NULL) you get: ERROR: regcomp failed with error empty (sub)expression Good to know. I had expected that regular expressions, like other operators, would return NULL if NULL was the pattern. Is this a bug? -Josh Berkus __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 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] What do I do with this error?
"Josh Berkus" <[EMAIL PROTECTED]> writes: > Never mind. It turns out that if you call try to feed a NULL to a > regular expression (e.g. Client_name ~* NULL) you get: > ERROR: regcomp failed with error empty (sub)expression > Good to know. I had expected that regular expressions, like other > operators, would return NULL if NULL was the pattern. Is this a bug? Yes, I'd say so. 7.1 returns a NULL as expected ... regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] hierarchical order equivalent
Is there an equivalent way to do this Oracle query in Postgres? Select category_id, parent_category_id, category_name FROM Category START WITH category_id = 6 CONNECT BY PRIOR category_id = parent_category_id Order by category_name I am trying to build a hierarchy of inter-related categories. I want an easy way to output the tree. Any ideas? Thanks, Grant ---(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] What do I do with this error?
Tom, > Yes, I'd say so. 7.1 returns a NULL as expected ... Er, Tom, I'm using 7.1. Pgsql does return a null as expected when going from the command line. However, if I call the regular expression from within a PL/pgSQL function, it bombs. Therefore, please forward to pgsql-bugs: Bug Name: Regular Expressions with NULL error out in PL/pgSQL. Can Reproduce: Reliably Severity: Annoyance. Location: PL/pgSQL / Function handler / SPI Version: 7.1 beta 3 Description: If you attempt to run a query within a PL/pgSQL function which compares a field. via regular expression, to a NULL variable, you get a fatal error. Example: CREATE FUNCTION test1 () RETURNS INT4 AS 'DELCARE expression1 VARCHAR; return_value INT4; BEGIN expression1 := NULL; SELECT id INTO return_value FROM table1 WHERE field2 ~* expression1; RETURN return_value; END; -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 ---(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] What do I do with this error?
Using the below (with a few fixes, delcare->declare and adding the close quote and language type as plpgsql, and a table1 defined as id int, field1 varchar, field2 varchar, I get a NULL returned if I select test1(); I'm somewhere between beta4 and 5 though. On Sun, 18 Mar 2001, Josh Berkus wrote: > Tom, > > > Yes, I'd say so. 7.1 returns a NULL as expected ... > > Er, Tom, I'm using 7.1. > > Pgsql does return a null as expected when going from the command line. > However, if I call the regular expression from within a PL/pgSQL > function, it bombs. > > Therefore, please forward to pgsql-bugs: > > Bug Name: Regular Expressions with NULL error out in PL/pgSQL. > Can Reproduce: Reliably > Severity: Annoyance. > Location: PL/pgSQL / Function handler / SPI > Version: 7.1 beta 3 > > Description: If you attempt to run a query within a PL/pgSQL function > which compares a field. via regular expression, to a NULL variable, you > get a fatal error. > > Example: > > CREATE FUNCTION test1 () RETURNS INT4 AS > 'DELCARE > expression1 VARCHAR; > return_value INT4; > BEGIN > expression1 := NULL; > SELECT id INTO return_value > FROM table1 > WHERE field2 ~* expression1; > RETURN return_value; > END; ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] What do I do with this error?
"Josh Berkus" <[EMAIL PROTECTED]> writes: > Er, Tom, I'm using 7.1. > Pgsql does return a null as expected when going from the command line. > However, if I call the regular expression from within a PL/pgSQL > function, it bombs. Well, that's more interesting, but I still can't reproduce it ... regression=# \df+ test1 List of functions Result | Function | Arguments | Owner | Language | Source | Description -+--+---+--+--+- ---+ - integer | test1| | postgres | plpgsql | declare expression1 VARCHAR; return_value INT4; BEGIN expression1 := NULL; SELECT id INTO return_value FROM table1 WHERE field2 ~* expression1; RETURN return_value; END; | (1 row) regression=# create table table1 (id int, field2 text); CREATE regression=# select test1(); test1 --- (1 row) regression=# insert into table1 values(1,'zz'); INSERT 1273361 1 regression=# select test1(); test1 --- (1 row) regression=# insert into table1 values(1,null); INSERT 1273362 1 regression=# select test1(); test1 --- (1 row) regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
