Re: [SQL] Table Pivot
On Thursday 13 Feb 2003 10:48 am, V. Cekvenich wrote: > How do you do a table Pivot in PostgreSQL? Hi, I've noticed that you've posted this twice now with no response. I can't help you because I don't know what you mean by doing a 'table pivot', but maybe if you describe what you're trying to do I'll see if I can help Gary > > tia, > .V > > > = > > > ---(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 -- Gary Stainburn This email does not contain private or confidential material as it may be snooped on by interested government parties for unknown and undisclosed purposes - Regulation of Investigatory Powers Act, 2000 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] rownum
Joe Conway <[EMAIL PROTECTED]> writes: > I've played with this a bit in the past, and concluded that the best way > to do it (if in fact you agree it should be done at all), would be to > add a rownum pseudo column as the results are projected from the backend > to the frontend. I think this would require a change to the FE/BE > protocol, which we've talked about doing for 7.4. Considering that it can trivially be done on the client side, I think it would be a hard sell to put such a wart into the protocol ... 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])
[SQL] Timezone conversion
Is there a straightforward way to change the display of a time to another timezone? For example, my server's environment is EST (-0500); I want to convert timestamps for display in PST (-0800). What I've been doing is to use a Pl/Pgsql function to subtract the current timezone-offset and add in the desired one. Is there a way to do it without arithmetic? ---(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] sub-query optimization
On 14 Feb 2003, Brad Hilton wrote: > I am hoping someone can help explain why modifying the following query > can effect such a huge change in speed. The query is: > > select * from articles > where exists >( select 1 from article_categories > where > article_categories.article_id = articles.id and > article_categories.category_id is null >) > > The original query was much more complex, but I have trimmed it down to > highlight the problem. The query above also manifests the problem. OK, > the above query (with 100,000 records in the articles table) takes 1292 > msec (see output below). If I modify the query slightly: > > > select 1 from article_categories > --> > select 1 from articles, article_categories > - After putting the latter in the subselect do you actually have the same query? In one case articles is an outer reference for the particular row. In the other it's a reference to the copy of articles in the subselect. Wouldn't that give the wrong results when you have any matches (since there'd exist a row from the subselect even if it wasn't the one matching the outer query)? ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Timezone conversion
Stephan Szabo <[EMAIL PROTECTED]> writes: > On Thu, 13 Feb 2003, Jeff Boes wrote: >> Is there a straightforward way to change the display of a time to another >> timezone? For example, my server's environment is EST (-0500); I want to >> convert timestamps for display in PST (-0800). > I think you might be able to use "AT TIME ZONE" to do what you want. I > think it's described in Chapter 6 of the user's guide. Also, there's always the straightforward approach: temporarily change the TIMEZONE variable ;-) I believe AT TIME ZONE is new in 7.3. regards, tom lane ---(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] postmaster -i & establishes three connections why?
Hi, > > I've just upgraded my cygwin to 1.3.20-1 and postgresql to 7.3.2 > > And I found that when run > > postmaster -i & > > There are three connections being established and why is that? > > Administrator@DemoServer ~ > $ LOG: database system was shut down at 2003-02-13 17:33:26 EST > LOG: checkpoint record is at 0/841330 > LOG: redo record is at 0/841330; undo record is at 0/0; shutdown TRUE > LOG: next transaction id: 484; next oid: 16976 > LOG: database system is ready > $ ps -l > PIDPPIDPGID WINPID TTY UIDSTIME COMMAND > 2292 12292 2292 con 500 17:43:20 /usr/bin/bash > 232022922292 2324 con 500 17:43:22 > /usr/local/bin/ipc-daemo > n > 209222922292 2404 con 500 17:43:22 > /c/progra~1/apache~1/apa > che/cgi-bin/printspool > 231222922292 2452 con 500 17:43:24 > /usr/bin/postgres > 204823122292 2048 con 500 17:43:28 > /usr/bin/postgres > 188420482292 1884 con 500 17:43:28 > /usr/bin/postgres > 250422922504 1960 con 500 17:43:36 /usr/bin > Are you sure those are connections. On server start you should get three process going, the main postmaster, the stats collector, and the stats buffer Robert Treat ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] sub-query optimization
On Fri, 2003-02-14 at 14:08, Tom Lane wrote: > Brad Hilton <[EMAIL PROTECTED]> writes: > > ... If I modify the query slightly: > > > > > select 1 from article_categories > > --> > > select 1 from articles, article_categories > > - > > > the query takes 98 msec. > > Yeah, because then the sub-query is a constant (it doesn't depend on the > current outer row at all) and so it is only evaluated once, not once per > outer row. Unfortunately, that approach probably gives the wrong > answers... Ah, that makes sense. But does it surprise you that when I manipulate the dataset such that the inner query matches 0 records, the total query takes so much longer? Unfortunately, after following the suggestions of several kind posters, the resulting queries are pretty slow compared to my example which used 'exists.' The fact that the query takes so long in certain dataset conditions is surprising me. Watch the following results: psql> update categories set restrict_views = FALSE; explain analyze select * from articles where exists (select 1 from article_categories, categories where article_categories.article_id = articles.id and categories.restrict_views = FALSE and article_categories.category_id = categories.id ) and post_status = 'publish' order by publish_time desc limit 10; Total runtime: 0.69 msec psql> update categories set restrict_views = TRUE; explain analyze select * from articles where exists (select 1 from article_categories, categories where article_categories.article_id = articles.id and categories.restrict_views = FALSE and article_categories.category_id = categories.id ) and post_status = 'publish' order by publish_time desc limit 10; Total runtime: 27490.84 msec Is that a surprising result? I would think that the second time things would be faster because there are no matches to the inner query. In fact, if I execute the inner query by itself, minus the reference to the articles table, it executes lightning fast. (0.07 msec) -Brad ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] sub-query optimization
On Fri, 2003-02-14 at 04:59, Tomasz Myrta wrote: > Brad Hilton wrote: > > > select * from articles where exists > > (select 1 from article_categories, categories, category_map > >where > >article_categories.article_id = articles.id and > >categories.restrict_views = FALSE and > >article_categories.category_id = categories.id and > >category_map.parent_id = 1 and > >category_map.child_id = categories.id and > >category_map.child_id = article_categories.category_id > > ) > > and > > post_status = 'publish' > > According to your table definition I can say, that you don't need subselect > and exists, because 1 row from article and 1 row from categories have only 1 > hit row in articles_categories (primary key), I don't think the article_categories primary key can be used in my query since I'm also joining against category_map. Articles can live in multiple categories. What my query is attempting is (in english terms): select all articles that live in non-restricted categories at or below a top-level category (id=1 in this case). If I just utilize article_categories primary key, I could end up with duplicate articles since articles can live in multiple categories. > so you can rewrite your query > as simple joins: > (Query is only a hint, it probably won't work) > > select a.* > from > categories_c cross join category_map m > join articles a on (child_id=category_id) > join articles_categories ac using (article_id,category_id) > where > m.parent_id=1 and not c.restrict_views; > and a.post_status='publish' > In case I'm not understanding your suggestiong perfectly, I tried to flesh it out a bit more. Does the following query match your suggestion? select a.* from categories c cross join category_map m join article_categories ac on (c.id = ac.category_id and m.child_id = ac.category_id) join articles a on (a.id = ac.article_id) where m.parent_id=1 and not c.restrict_views and m.child_id = c.id and a.post_status='publish' Unfortunately, this query returns duplicate articles (see explanation above), and is fairly slow. Maybe I didn't follow your initial query properly. -Brad ---(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] PL/PGSQL EDITOR
I use Editplus (http://www.editplus.com/) with PosgreSQL syntax highlighting (ftp://ftp.editplus.com/files/postgre.zip). I also define psql as external tool in Editplus and use shortcut (Ctrl + 1) to send file to server. Tambet - Original Message - From: "Eduardo" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Thursday, February 13, 2003 11:20 PM Subject: [SQL] PL/PGSQL EDITOR > Do exists a PL/PGSQL editor? > > Thanks. > > > > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] > ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
