[SQL] 2 Selects 1 is faster, why?
If I perform the following 2 selects, the first one is EXTREMELY slow where the 2nd one is very fast. (1) Slow select o.orderid, ol.itemcode, ol.itemname from orders o, orlines ol where o.orderid = '1234' and ol.orderid = o.orderid; (2) VERY FAST select o.orderid, ol.itemcode, ol.itemname from orders o, orlines ol where o.orderid = '1234' and ol.orderid = '1234' Why would 2 be so much faster? I have ran the EXPLAIN on this and index scans are being used. NOTE: The actual queries return more information than this, but the fundamental change shown above seems to give me the instant response I am looking for. (1) takes about 60 seconds to run and (2) takes 3-5 seconds to run. Thanks, Eric ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Performance Ideas
I have a SQL which uses a function for one of the returned rows. This stored function does calculations that are expensive & slow. I am looking for ways to speed up this query but having no luck. Any SQL geniuses out there help me with this? select o.orderid, ol.itemcode, ol.itemname, ol.uom, qty_available( ol.itemcode, ol.uom ) as "Qty On Hand" from orders o, orderlines ol, where o.status = 'OPEN' and ol.orderid = o.orderid and qty_onhand( ol.itemcode, ol.uom ) > 0; The function, qty_onhand, calculates the Qty on hand and returns a value in units of measure passed (ol.uom). This function is an expensive function to use -- degrades performance. With out the function in the WHERE or SELECT clause, performances is acceptable. I get marginally better performance if I "select into temporary table" without the function and then run a query on the temporary table which includes the qty_onhand function. I am trying to present the user with a list of open orders that are "READY" to be fulfilled which requires me to do a "stock level check." My fall back solution is to make the user enter some pre-query information like the orderid she is trying to ship against but my customer really likes the current view they have which shows all open orders that are READY to be fulfilled. Any ideas??!?!?! Tricks of the trade?!?!?! Also, side note, I tried creating views assuming PostgreSQL would optimize the view after a vacuum but it does not. Also, the function seems faster in the temporary table, why? Why wouldn't the funciton only evaluate values that match the first 2 criteria (OPEN and ol.orderid = o.orderid)? It's as if the qty_onhand is evaluating ALL records in the orderlines (ol) table. Thanks , Eric ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Limiting database size
I like the idea of putting it on a hard disk or partition of fixed size and waiting for the DB to simply crash. hahaha "Josh Berkus" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED]... > Mauricio, > > > Hi, is there any way to limit the database size?? > > First, this is a question for PGSQL-NOVICE or PGSQL-GENERAL, not this mailing > list. > > Second -- sort of. You can put the database on its own hard drive partition. > Then the database will crash when it runs out of space -- so I'm not sure > that helps you. > > Of course, with other RDBMSs, which have administrative settings for size > limits, the database shuts down when you're out of space. So I'm not sure > how useful the whole idea is. > > -- > -Josh Berkus > > __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 > > > ---(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 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] Querying Hierarchical Data
Hi, How do I access hierarchical data under PostgreSQL? Does it have SQL command similar to Oracle's CONNECT BY? Any help is appreciated Eric ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] Localization
Hi all Is there a simple way to localize in foreign language error messages without modifying and compiling the sources again ? It should be useful for final users who don't read Shakespeare in the original version ;) Eric GRIMOIS Analyste programmeur SEI - CPAM du Val d'Oise ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Query kill
On Fri, 12 Jul 2002 01:01:31 -0400 (EDT) in message <[EMAIL PROTECTED]>, Bruce Momjian <[EMAIL PROTECTED]> wrote: > Rudi Starcevic wrote: > > Hello, > > > > If I write a query that is inefficient or in an eternal loop how > > do I stop it without restarting the postmaster ? > > > > I can see many postmaster processed appearing in the output of the 'ps' > > command. > > Do I need to stop/kill them all or can I stop just the query I want ? > > Just send a SIGINT to the process. That simulates a ^C, which works too > from the client like psql. Is there a way to deny permission for certain users to execute a query that exceeds some expected cost? For example, I have a query builder from user input that could produce a query that ends up doing something that the query planner thinks will take 8M units of work. Generally, this is an unconstrained join between my biggest tables, a result that is neither fast nor useful. If I could set a threshold of 1M units for the webapp user, I could trap this sort of thing before they cause quality of service issues. eric ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] select question
On Wed, 28 Aug 2002 16:12:41 -0400 in message
<[EMAIL PROTECTED]>, george young <[EMAIL PROTECTED]> wrote:
> [postgreql 7.2, linux]
> I have a table T with columns run, wafer, and test:
>T(run text, wafer int, test text)
> Given a run and a set of wafers, I need the set of tests that match
> *all* the specified wafers:
>
> run wafer test
> a 1 foo
> a 2 foo
> a 3 foo
> a 3 bar
>
> E.g.
> Given run 'a' and wafers (1,3) I should get one row: foo, since only foo matches
>both 1 and 3.
> Given run 'a' and wafers (3) I should get two rows: foo,bar, since both foo and
>bar match 3.
>
> Is there some neat way to do this in a single query?
>
select test from T
where run='a'
and wafers in ('1','3')
group by test
eric
---(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] Hairy question - transpose columns
> I'm sure there's a better way, but I think a series of union alls would > do it but be rather computationally expensive. > > select cod_var, Year, Month, 1 as Day, RainDay1 as Rain > where Ten=1 > union all > select cod_var, Year, Month, 2 as Day, RainDay2 as Rain > where Ten=1 You could do the following: select cod_var, Year, Month, 1+((ten-1)*10) as Day, RainDay1 as Rain where RainDay1 is not null union all select cod_var, Year, Month, 2+((ten-1)*10) as Day, RainDay2 as Rain where RainDay2 is not null .. I'm sure that there is a function that could do this too, but I'd tend to just convert the data and be done with it. eric ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Row Locking?
I have a long running process that performs outside actions on the content of a table. The actions could all be done in parallel (if I had n processors), but I need to ensure that the process is attempted exactly one time per applicable row. My current design for one thread is the following (simplified to a test case) create table foo (pending boolean, done boolean, idx serial); select * from foo where pending='f' and done='f' limit 1; update foo set pending='t' where idx=[[returned idx]]; commit; do stuff outside database update foo set pending='f', done='t' where idx=[[returned idx]]; commit; Extending this to multiple threads if proving problematic. No locking leads to a race condition between the select and update. If I change the select to a SELECT ... FOR UPDATE it apparently locks the table against all other select for updates, then when the update is committed, the second thread returns nothing, even when there are other rows in the table that could be returned. Is there a single row locking against select? Or can I effeciently do the equivalent of update set pending, then select the row that I just updated to get the contents? (perhaps without doing a table scan to find the oid of the row that I just updated). I can't afford to lock the entire table. eric ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] Query for filtering records
I'm having trouble subtracting groups from other groups. I've got a data model that has the following essential features: create table contacts (num int, properties); create table groups (groupNum int, contactNum int); Where not all contacts will be in a group, some groups will contain most contacts, and there will be something like hundreds of groups and tens of thousands of contacts. I allow people to build groups using criteria, which I need to programatically translate to sql. One somewhat common pattern is: Select all contacts in group a, who have property b, and who aren't in groups c,d,e,f... My first shot was subqueries: select num, p1,p2 ... from contacts inner join groups using (contacts.num=groups.contactNum) where groups.groupNum=a and contact.p3=b and not num in (select contactNum from groups where groupNum=c) and not num in (select contactNum from groups where groupNum=d) and not num in (select contactNum from groups where groupNum=e) and not num in (select contactNum from groups where groupNum=f) This is slow. agonizingly so. With an inner join, I'm not convinced that the subtraction is actually correct., but it is much faster. Unfortunatley, faster incorrect answers are rarely helpful. Outer joins seem even worse than subselects for speed, but it does appear to give the correct answer. (example with a single join.) select num from contacts left outer join groups on (contacts.num=groups.contactNum and groups.groupNum=b) where dl_groupDonor._groupNum is null and p3=c I've got to be missing something here, because this is much slower from the (slow) procedural system that I'm porting from. I've been avoiding using union / intersect since I don't really ever know what columns are going to be in the query. perhaps I should revisit that decision and try to work around it. eric ---(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] Query for filtering records
> SELECT * ... > FROM ... > WHERE NOT IN (SELECT contactnum FROM groups WHERE groupnum='c' or > groupnum='d' OR ... ) > > is bound to be _much_ faster! Yeah, that's an obvious optimization. Unfortunately, due to needing to match semantics of a previous non-sql version and some pathological group specifications from clients, it can't be applied as often as I'd like. Yes, I call client specs pathological sometimes. No, they don't know that. > And even better is > > SELECT * > FROM ... contacts c1 > WHERE NOT EXISTS (SELECT * FROM groups WHERE groupname='c' or > groupnum='d' or groupnume='e' ... AND groups.contactnum=c1.contactnum) > > > EXISTS is almost always faster in PG. Exists seems to be the answer. It even gives the right answer, which has been a problem for queries of this sort. Rewriting the queries so that the subtraction clauses use exists are giving me reasonable runtimes (~5 sec) and believable answers. (instead of the other two extremes of 5 minutes and either no answers or everyone in the database) What's useful here is that I'm getting multiple exists index scans instead of nested loops or table scans. What's more, exists clauses are really easy to integrate into my query generation routine. thanks eric ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] cast of integer to bool doesn't work (anymore?)
On Fri, Mar 21, 2003 at 04:46:17PM -0200, Achilleus Mantzios wrote:
>
> Currently (7.3) all input can be handled if fed as text.
> So what you can do is simply:
>
> my $sth = $dbh->prepare(
> "INSERT INTO object_def (name, meant_as_subobject) VALUES (?,?)");
> $sth->execute('test', '0');
Ah, thanks, that seems to work, with only minor modifications to our
code. Is there any reason why integers are no longer convertable to
booleans?
--
Eric Veldhuyzen
xs4all NSA team
pgp0.pgp
Description: PGP signature
Re: [SQL] locks and variable substitution
On Fri, 2003-07-25 at 11:49, [EMAIL PROTECTED] wrote: > > lock table excl_table in exclusive mode; That probably wont work, but this will: EXECUTE ''LOCK TABLE '' || quote_ident(excl_table) || '' IN EXCLUSIVE MODE''; Eric ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] One to many query question
On Wed, 2003-07-30 at 12:35, Dave Dribin wrote: > CREATE TABLE cd ( > id integer unique, > artist varchar(25), > title varchar(25) > ); > > CREATE TABLE cd_genres ( > cd_id integer, > genre varchar(25) > ); I think you've got this backwards. There is no advantage in the above table's over simply having a genre varchar(25) in the cd table. You really want: CREATE TABLE genre ( genre_id serial, genre varchar(25) ); CREATE TABLE cd ( cd_id integer unique, artist varchar(25), title varchar(25), genre_id varchar(25) references genre (genre_id) ); > How do I write a query to find all CDs that are NOT Rock? A co-worker > showed me the following query: Now the query is simple: SELECT cd.*, genre.genre FROM cd, genre WHERE cd.genre_id = genre.genre_id AND genre.genre != 'Rock'; Hope that helps, Eric ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] One to many query question
> This doesn't allow multiple genre's per CD, though, does it? A CD > can only have 1 genre_id. I would like the ability to have multiple > genres, in which case a third table is necessary: > > CREATE TABLE cd_genres ( > cd_id integer, > genre_id integer > ); > > cd_id references cd.id and genre_id references genre.genre_id. > > This still requires the complex LEFT JOIN query from my first post, > too, I think, *plus* an extra join between cd_genres and genre. Sorry, the cd_genre table would be the way to do it. This was a fairly complex problem so I created the tables in a test database and wrote a few queries that I think solve the problem for you, depending on how you want select to return the genre list. Here's a couple queries that will only get cd's that are not part of Rock. SELECT c.* FROM cd AS c WHERE 'Rock' NOT IN (SELECT g.genre FROM genre AS g, cd_genre AS cg WHERE g.genre_id = cg.genre_id AND cg.cd_id = c.cd_id); SELECT c.* FROM cd AS c WHERE NOT EXISTS (SELECT NULL FROM genre AS g, cd_genre AS cg WHERE g.genre_id = cg.genre_id AND cg.cd_id = c.cd_id AND g.genre = 'Rock'); The second one should be faster. The next query will show all cd's that not exclusively Rock. (OK Computer should be in the result) SELECT c.* FROM cd AS c, cd_genre AS cg, genre AS g WHERE c.cd_id = cg.cd_id AND cg.genre_id = g.genre_id AND g.genre != 'Rock'; If you add g.genre to any of the above queries you will get one row per cd+genre combination. I dont know of any way to make that a delimited list other than writing a function. So I wrote one for fun. The argument is the cd_id. CREATE OR REPLACE FUNCTION genre_list (integer) RETURNS TEXT AS ' DECLARE cdid ALIAS FOR $1; return_val TEXT; r RECORD; BEGIN FOR r IN SELECT g.genre FROM genre AS g, cd_genre AS cg WHERE g.genre_id = cg.genre_id AND cg.cd_id = cdid LOOP IF return_val IS NULL THEN return_val := r.genre; ELSE return_val := return_val || '', '' || r.genre; END IF; END LOOP; RETURN return_val; END ' LANGUAGE 'plpgsql' STABLE RETURNS NULL ON NULL INPUT SECURITY INVOKER; Now I see that while composing this email some others have replied with more simple solutions. For your case I prefer the added tables as they enforce the possible list of genre's. Its also handy to keep them seperate to get the list of genre's to display in a UI. Eric ps: aliasing all the table names is just my habit, do it however you see fit. I also dont like to make my table names plural, its implied. ---(end of broadcast)--- TIP 8: explain analyze is your friend
[SQL] Using contrib/fulltext on multiple tables
I am running into a problem with using contrib/fulltext on several tables. Here's the details... I have one sql script which drops everything and recreates the database structure, then populates it with some test data. In this script I create tables a,b and c and setup the triggers for fulltext indexing (as well as associated tables for the index data). Later in the script when inserting into these tables via stored procedures, the full text indexes get created for a and b but not c. It's just empty. If I just have a and c setup in the script, they both work. Same for a and b. It seems to be a problem with b and c. Something else that is odd is I can then call the Insert stored procedure for c and it's index doesn't get created. However, if I exit and reenter the psql client app, THEN run the Insert stored procedure, the index is created just fine. I've tried breaking it into two scripts (one for db structure and one for test data) but it still doesn't do the indexing on c. I apologize for the length, Eric ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] SRF Functions don't want to return empty tuple
Hi I have a problem with SRF functions on a RedHat 8.0 system with PostgreSQL 7.3.2. Schema is following : CREATE TABLE public.agents ( numero_age int4 NOT NULL, nom_age varchar(30) NOT NULL, prenom_age varchar(30) NOT NULL, date_entree_age date NOT NULL, identite varchar(50), CONSTRAINT agents_pkey PRIMARY KEY (numero_age) ) WITH OIDS; INSERT INTO public.agents VALUES (7, 'PERAVIF', 'JO', '1967-06-18', '') CREATE FUNCTION public.liste_agents(int4) RETURNS public.agents AS 'SELECT * FROM agents WHERE numero_age = $1' LANGUAGE 'sql' VOLATILE; When, I try : SELECT * FROM liste_agents(7) Everything is Ok, I get my agent. But if I try : SELECT * FROM liste_agents(0) (... or any other numero_age not in the table...) I get a error message : ExecMakeTableFunctionResult: Invalid result from function returning tuple What's wrong ? I guessed null values returned by the query didn't match fields declared not null in the table schema, so I create a custom type (with CREATE TYPE) but it neither doesn't work. Hint (?) : I've activated Plpython for this Database. Thanks Eric GRIMOIS Concepteur de logiciels SEI - CPAM du Val d'Oise ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] SRF Functions don't want to return empty tuple
- Original Message - From: "Stephan Szabo" <[EMAIL PROTECTED]> To: "GRIMOIS Eric" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Monday, September 29, 2003 6:40 PM Subject: Re: [SQL] SRF Functions don't want to return empty tuple > > On Mon, 29 Sep 2003, GRIMOIS Eric wrote: > > > Hi > > > > I have a problem with SRF functions on a RedHat 8.0 system with PostgreSQL > > 7.3.2. > > > > Schema is following : > > > > CREATE TABLE public.agents ( > > numero_age int4 NOT NULL, > > nom_age varchar(30) NOT NULL, > > prenom_age varchar(30) NOT NULL, > > date_entree_age date NOT NULL, > > identite varchar(50), > > CONSTRAINT agents_pkey PRIMARY KEY (numero_age) > > ) WITH OIDS; > > > > INSERT INTO public.agents VALUES (7, 'PERAVIF', 'JO', '1967-06-18', '') > > > > CREATE FUNCTION public.liste_agents(int4) RETURNS public.agents AS 'SELECT * > > FROM agents WHERE numero_age = $1' LANGUAGE 'sql' VOLATILE; > > Are you sure you don't want setof public.agents if you want to be able to > return an empty set? > Yes, you're right. Now, it works. Thank you very much The syntax I used was wrong, but is accepted by Postgres. What does it mean ? ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Is there a more elegant way to write this query?...
On Nov 10, 2003, at 1:02 PM, Nick Fankhauser wrote: Hi- I'm suffering from a performance problem, but when I look at my query, I'm not convinced that there isn't a better way to handle this in SQL. -So I'm seeking advice here before I go to the performance list. An explain analyze would help. What I'm trying to do is link these tables to get back a single row per actor that shows the actor's name, the number of cases that actor is assigned to, and if they only have one case, I want the number for that case. This means I have to do some grouping to get the case count, but I'm then forced to use an aggregate function like max on the other fields. I hope there's a better way. Any suggestions? How about: select actor.actor_full_name, actor.actor_id, s1.ctCases, s1.case_id, case_data.case_public_id from actor inner join ( select actor_id, count(*) as ctCases, max(case_id) as case_id from actor_case_assignment group by actor_id) as s1 on (actor.actor_id = s1.actor_id) left outer join case_data using (s1.case_id=case_data.case_id) limit 1000; If you don't need the public_id, then you don't even need to join in the case data table. eric ---(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] Datetime problem
Hello
there,
I'm with a little
trouble with postgresql and date/time conversions:
- select
to_timestamp('2004 10 10 00 00 00', ' MM DD HH MI SS')
the output
is:
- 2004-10-09
23:00:00-03
Anybody can help
me?
[]'s
Eric
Lemes de Godoy Cintra
Analista
de Sistemas Líder Zanthus
Sistemas e Equip. Automação S/A Desenvolvimento
de Software
[EMAIL PROTECTED] Fone:
55(11) 3750-7077
<>
RES: [SQL] Datetime problem
Hello,
- PostgreSQL 7.3.2 on i386-redhat-linux GCC 3.2.2
- Timezone: Brazil (GMT-3, I think).
I think my problem is with the time zone. Using a SET TIME ZONE GMT, the
result is Ok. But I don't know how to work with time zones correctly.
When I send a date to to_timestamp, pgsql thinks this date is in GMT?
Thanks for all your help.
[]'s
Eric Lemes
> -Mensagem original-
> De: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] nome de Tom Lane
> Enviada em: segunda-feira, 14 de junho de 2004 11:56
> Para: Eric Lemes
> Cc: [EMAIL PROTECTED]
> Assunto: Re: [SQL] Datetime problem
>
>
> "Eric Lemes" <[EMAIL PROTECTED]> writes:
> > - select to_timestamp('2004 10 10 00 00 00', ' MM DD HH MI SS')
> > the output is:
> > - 2004-10-09 23:00:00-03
>
> What PG version is this, on what platform, and what's your current
> timezone setting?
>
> regards, tom lane
>
> ---(end of broadcast)---
> TIP 7: don't forget to increase your free space map settings
> ---
> Incoming mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.693 / Virus Database: 454 - Release Date: 31/5/2004
>
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.693 / Virus Database: 454 - Release Date: 31/5/2004
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
[SQL]How to transform table rows into Colum?
Hi Guys, I have the following table: Name Value rang salary name1 value1 12500 name2 value2 22600 name3 value 3 3300 and want to obtain the following result: name1 name2 name3 value1 value2 value3 1 23 2500 2600300 what should i do ? I read about the crosstab function in postgresql but still have no idea on how to use it to solve this problem. Any help would be appreciated Regards -- Eric Ndengang Datenbankadministrator Affinitas GmbH | Kohlfurter Straße 41/43 | 10999 Berlin | Germany email: [email protected] | tel: +49.(0)30. 991 949 5 0 | www.edarling.de Geschäftsführer: Lukas Brosseder, David Khalil, Kai Rieke, Christian Vollmann Eingetragen beim Amtsgericht Berlin, HRB 115958 -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] column type for pdf file
On Wed, May 18, 2011 at 2:20 PM, Emi Lu wrote: > Hello, > > To save pdf files into postgresql8.3, what is the best column type? > > bytea, blob, etc? > > Thank you, > Emi > Everyone else has pointed out reasons for not doing this, and I agree with them that in the large majority of cases just storing a reference to a file stored outside the database is preferable. However, to answer the question you asked, my rule of thumb is that if you need to store binary data in the database is to use a bytea column, unless you need the random access capabilities that the large object interface provides. A bytea column is typically easier to use, and has proper transactional behavior, enforcement of referential integrity, etc. -Eric
[SQL] Help with SQL
I'm new to postgres. I've got 2 SQL statements I would like to combine into one. I think I need to use a sub select or join I am not sure. Any help would be appreciated! statement 1: SELECT uid, count(uid) FROM triangulated WHERE uid != 'anonymus' AND uid!= 'anonymous' AND uid != '' GROUP BY uid ORDER BY count DESC LIMIT 10; that returns something like this: uid | count-+--- eblevins | 1179 DaClyde | 398 Drew | 30 zombiechick | 3(4 rows) statement 2: SELECT uid, count(uid) FROM points WHERE uid != 'anonymus' AND uid !='anonymous' AND uid != '' GROUP BY uid ORDER BY count DESC LIMIT 10; that returns something like this: uid | count-+--- eblevins | 23595 DaClyde | 11031 zombiechick | 159 Drew | 104(4 rows) what I want to do is have one statement that returns something like this: uid | count1 | count2 eblevins 1179 23595 DaClyde 398 11031 Drew 30 104 zombiechick 3 159 So everything is ordered like statement 1 but includes the count(uid) from the points DB like statement 2 returns Any ideas on an effecient way of doing this?
Re: [SQL] Help with SQL
This is what I ended up with: SELECT c1.uid, count1, count2 FROM (SELECT uid, count(uid) AS count1 FROM triangulated WHERE uid != 'anonymus' AND uid != 'anonymous' AND uid != '' GROUP BY uid) AS c1 LEFT JOIN (SELECT uid,count(uid) AS count2 FROM points WHERE uid != 'anonymus' AND uid != 'anonymous' AND uid != '' GROUP BY uid) as c2 on (c1.uid = c2.uid) ORDER BY count1 DESC LIMIT 10; I got the results I wanted! uid | count1 | count2 -++ eblevins| 1179 | 23595 DaClyde |398 | 11031 Drew| 30 |104 zombiechick | 3 |159 (4 rows) Thanks for your help! Eric L. Blevins - Original Message - From: "Oliver Elphick" <[EMAIL PROTECTED]> To: "Eric L. Blevins" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Wednesday, October 16, 2002 4:38 PM Subject: Re: [SQL] Help with SQL > On Wed, 2002-10-16 at 19:26, Eric L. Blevins wrote: > > I've got 2 SQL statements I would like to combine into one. > ... > > statement 1: SELECT uid, count(uid) FROM triangulated WHERE uid != 'anonymus' AND uid > > != 'anonymous' AND uid != '' GROUP BY uid ORDER BY count DESC LIMIT 10; > ... > > statement 2: SELECT uid, count(uid) FROM points WHERE uid != 'anonymus' AND uid != > > 'anonymous' AND uid != '' GROUP BY uid ORDER BY count DESC LIMIT 10; > ... > > what I want to do is have one statement that returns something like this: > > uid|count1| count2 > > eblevins117923595 > > DaClyde39811031 > > Drew30104 > > zombiechick3159 > > > > So everything is ordered like statement 1 but includes the count(uid) from the points DB like statement 2 returns > > SELECT * FROM >(SELECT uid, count(uid) AS count1 > FROM triangulated > WHERE uid != 'anonymus' AND >uid != 'anonymous' AND >uid != '' > GROUP BY uid) AS c1 >LEFT JOIN >(SELECT uid, count(uid) AS count2 > FROM points > WHERE uid != 'anonymus' AND >uid != 'anonymous' AND >uid != '' > GROUP BY uid) AS c2 > ORDER BY count1 DESC > LIMIT 10; > > (Apologies for syntax errors, if any - I haven't tried it out,) > > I used LEFT JOIN because you are ordering by count1, so you probably > won't want any rows where count1 is null. If the total of rows from > subselect c1 was likely to be less than 10, you might want to do a FULL > JOIN and order by count1, count2. > > -- > Oliver Elphick[EMAIL PROTECTED] > Isle of Wight, UK > http://www.lfix.co.uk/oliver > GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C > > "But be ye doers of the word, and not hearers only, > deceiving your own selves." James 1:22 > > ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] discover a toast table name
when i try that i´v got an error ERROR: "pg_toast_26474986" is a system table. call REINDEX under standalone postgres with -O -P options so I kill postmaster and up it standalone. but the reindex command doesn´t have options. and postmaster doesn´t have these ones too. tnx. Eric Anderson Martins Miranda Net Admin @ Via Net SAO - Original Message - From: "Tom Lane" <[EMAIL PROTECTED]> To: "Eric Anderson Vianet SAO" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Tuesday, May 27, 2003 11:20 AM Subject: Re: [SQL] discover a toast table name > "Eric Anderson Vianet SAO" <[EMAIL PROTECTED]> writes: > > How can I discover which toast table referenced to a phisic table? > > Look at the base table's pg_class row. reltoastrelid is the OID of the > corresponding toast table. So, for example, if I thought "text_tbl" had > such a problem: > > regression=# select relname from pg_class where > regression-# oid = (select reltoastrelid from pg_class where relname = 'text_tbl'); > relname > - > pg_toast_163219 > (1 row) > > regression=# reindex table pg_toast.pg_toast_163219; > REINDEX > regression=# > > 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]) ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[SQL] little doubt
Shoul I thank the guys who help me? Or to post other message saying ´it worked well etc´? or it will flood the lists? I´m new(bie) on the lists. tnx Eric Anderson Martins MirandaNet Admin @ Via Net SAO
[SQL] yet pg_toast reindex
When I try to reindex a toast table with command ´REINDEX TABLE pg_toast_16557' I receive following error message: ERROR: "pg_toast_16557" is a system table. call REINDEX under standalone postgres with -O -P options So I restart postmaster standalone (no -i option) and I receive same error. how could I reindex it? tnx Eric Anderson Martins Miranda Net Admin @ Via Net SAO ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] yet pg_toast reindex
Sorry about this sucks thread but now i´v got following error ERROR: relation "pg_toast_26474986" is of type "t" I looked for "type t" around documentation and don´t find something usefull. tnx Eric Anderson Martins Miranda Net Admin @ Via Net SAO - Original Message - From: "Tom Lane" <[EMAIL PROTECTED]> To: "Eric Anderson Vianet SAO" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Tuesday, June 17, 2003 11:55 AM Subject: Re: [SQL] yet pg_toast reindex > "Eric Anderson Vianet SAO" <[EMAIL PROTECTED]> writes: > > When I try to reindex a toast table with command ´REINDEX TABLE > > pg_toast_16557' I receive following error message: > > ERROR: "pg_toast_16557" is a system table. call REINDEX under standalone > > postgres with -O -P options > > So I restart postmaster standalone (no -i option) and I receive same error. > > Removing the -i option does not constitute a standalone backend. See > http://www.postgresql.org/docs/view.php?version=7.3&idoc=0&file=sql-reindex. html > for a detailed description of the procedure you need to follow here. > > (7.3 doesn't require this pushup anymore for toast tables, btw.) > > regards, tom lane > > ---(end of broadcast)--- > TIP 8: explain analyze is your friend ---(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] yet pg_toast reindex
which index? could you help me again? Eric Anderson Martins Miranda Net Admin @ Via Net SAO - Original Message - From: "Tom Lane" <[EMAIL PROTECTED]> To: "Eric Anderson Vianet SAO" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Tuesday, June 17, 2003 8:50 PM Subject: Re: [SQL] yet pg_toast reindex > "Eric Anderson Vianet SAO" <[EMAIL PROTECTED]> writes: > > Sorry about this sucks thread but now i´v got following error > > ERROR: relation "pg_toast_26474986" is of type "t" > > You may have to use REINDEX INDEX (on the index not the toast table of > course) to get it to work in older versions. REINDEX didn't use to > think it could work on toast tables. > > regards, tom lane > > ---(end of broadcast)--- > TIP 5: Have you checked our extensive FAQ? > >http://www.postgresql.org/docs/faqs/FAQ.html ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[SQL] unexpected chunk value
hello all When i tried ´pg_dump -v -f dump.dmp dtbtransporte´ I got the error: pg_dump: restoring data for table tbdmovimento pg_dump: dumping out the contents of table tbdmovimento pg_dump: ERROR: unexpected chunk number 8 (expected 0) for toast value 6935693 pg_dump: lost synchronization with server, resetting connection pg_dump: SQL command to dump the contents of table "tbdmovimento" failed: PQendcopy() failed. pg_dump: Error message from server: pg_dump: The command was: COPY "tbdmovimento" TO stdout; pg_dump: *** aborted because of error So I´ve ran a query to see which pg_toast from this tbdmovimento So I shutdown de postmaster and up a standalone backend and reindexed the database (REINDEX DATABASE dtbtransporte FORCE). All this after a vacuum analyze. How the pg_dump still returns above error, I´ve tried to REINDEX the index pg_toast_16557. Reindex ran ok, but the pg_dump still returns this error. any ideas. tnx Eric
[SQL] update system table?
how could I fix this problem: ERROR: unexpected chunk number 8 (expected 0) for toast value 6935693 It appear to be simple: update chunk_seq from 8 to 0. how to do it? in the stand alone postgres, could I copy a entire table? how to do it? tnx Eric
[SQL] view running query
I run an application which connects to my pgsql DB. How could I see which query is sent to DB when, an example, i push some application button (such ´find´). sds Eric Anderson CPD Via Net SAO 11-66432800 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] \D TO FILE
How could I record the ´ \d table ´ command to a file? sds Eric Anderson CPD Via Net SAO 11-66432800 ---(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
