Re: [SQL] [HACKERS] please help on query
On Fri, 12 Jul 2002 17:32:50 +0200 "Luis Alberto Amigo Navarro" <[EMAIL PROTECTED]> wrote: > Lineitem is being modified on run time, so creating a temp table don't > solves my problem > The time of creating this table is the same of performing the subselect (or > so I think), it could be done creating a new table, and a new trigger, but > there are already triggers to calculate > lineitem.extendedprice=part.retailprice*lineitem.quantity*(1+taxes)*(1-disco > unt) and to calculate orderstatus in order with linestatus and to calculate > orders.totalprice as sum(extendedprice) where > lineitem.orderkey=new.orderkey. A new trigger in order to insert orderkey if > sum(quantity) where orderkey=new.orderkey might be excessive. > Any other idea? > Thanks And Regards > > - Original Message - > From: "Jakub Ouhrabka" <[EMAIL PROTECTED]> > To: "Luis Alberto Amigo Navarro" <[EMAIL PROTECTED]> > Cc: "Manfred Koizar" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> > Sent: Friday, July 12, 2002 1:50 PM > Subject: Re: [SQL] [HACKERS] please help on query > > > > > avoid subselect: create a temp table and use join... > > > > CREATE TEMP TABLE tmp AS > >SELECT > > lineitem.orderkey > >FROM > > lineitem > >WHERE > > lineitem.orderkey=orders.orderkey > >GROUP BY > > lineitem.orderkey HAVING > > sum(lineitem.quantity)>300; Hi, I'm not sure whether its performance can be improved or not. But I feel there is a slight chance to reduce the total number of the tuples which Planner must think. BTW, how much time does the following query take in your situation, and how many rows does it retrieve ? EXPLAIN ANALYZE SELECT lineitem.orderkey FROM lineitem GROUP BY lineitem.orderkey HAVING SUM(lineitem.quantity) > 300; Regards, Masaru Sugawara ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Indexes with LIKE
On Sat, 13 Jul 2002, Julian Scarfe wrote: > From: "Stephan Szabo" <[EMAIL PROTECTED]> > > > You need to have made the database in C locale in order to get index scans > > from LIKE. I think that's mentioned in the Localization section of the > > admin guide, but I could be remembering that wrong. > > Thanks very much Stephan. Indeed it's in Admin Guide 5.1.2. I was using > en_GB, not C. > > There is of course no excuse for failing to read every bit of smallprint in > the admin guide before installation :-) but for those lazy unfortunates like > me that don't think to look there to find out what's wrong when indexes > aren't used with LIKE, I've added a DocNote under Pattern Matching. That's a good idea. Alot of the docs are written assuming that you're configuring it from source so that you'd have to turn on locale at configure time, but that's not true for package users. Are you actually using any of the locale features of the locale? If not, you might want to consider setting up in C locale. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] Quick Question
How do you get this to work? I'm creating a view of a table, but I'm trying to do something like this: create table t ( id integer not null, amount numeric(7,2) ); create view v as select id as v_id, 'paid amount: ' || amount as v_comment from t ; You get this: ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] Sorry..
Ignore previous half-completed email. How do you get this to work in 7.2.1? I'm creating a view of a table, but I'm trying to do something like this: create table t ( id integer not null, amount numeric(7,2) ); create view v as select id as v_id, 'paid amount: ' || amount as v_comment from t ; You get this: ERROR: Unable to identify an operator '||' for types 'unknown' and 'numeric' You will have to retype this query using an explicit cast None of these work: CAST(amount AS text) CAST(amount AS varchar) CAST(amount AS char) and this: CAST(amount AS real) works, but if amount is 12.00, then you just get '12' - which is not cool. Any ideas? Chris ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Sorry..
"Christopher Kings-Lynne" <[EMAIL PROTECTED]> writes: > [ there's no cast from numeric to text ] Feel free to contribute one. In the bad old days when we couldn't distinguish explicit from implicit cast functions, I was wary of adding new cast pathways. Too many implicit casts and you have no type system at all. But in 7.3 there should be no reason to object to an explicit-only cast from numeric to text or vice versa. 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
[SQL] Fwd: line datatype
Probably the most succinct explanation would be to copy & paste from the terminal... tjhart=> create table a_line( foo line ); CREATE tjhart=> insert into a_line ( foo ) values( '(0,0), (1,1)' ); ERROR: line not yet implemented tjhart=> select version(); version - PostgreSQL 7.2.1 on powerpc-apple-darwin5.3, compiled by GCC 2.95.2 (1 row) The documentation (datatype-geometric.html) indicates both a 'line' type and an 'lseg' type in the summary table at the top of the page. The same code above using the type 'lseg' in place of 'line' works just fine. Why can I create a table with a column of type 'line' if I can't insert into it? ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
