[SQL] please help with converting a view in oracle into postgresql readably code

2002-10-04 Thread Matthew Geddert
Hello, I am trying to convert an application to postgresql, and am having a bear of a time converting the following view (from oracle). What it does, just in case you aren't very familiar with oracle syntax, is group the average reg_state from the events_registrations table after having converted

[SQL] rows in order

2002-10-04 Thread Camila Rocha
Hi!   Is there a way to put in order the rows in a table? the problem is that i want to keep a "tree" in the db, but the leaves must be ordered... does someone have an idea?   thanks,   Camila

[SQL] Can Postgres cache a table in memory?

2002-10-04 Thread Kevin Traub
Hello all; I'm trying to speed up a query which returns the majority of a table so and index isn't helpful. I've got more than enough RAM to hold my table so, can anyone tell me if there is there a way to force Postgres to cache a table in RAM? Any help would be appreciated. Thanks; -Kevin Trau

[SQL] How slow is distinct - 2nd

2002-10-04 Thread Michael Contzen
Hello, I posted some observations to the performance of postgres some weeks ago. The problem with the poor performance of "select distinct" still exists, but I tried to worked out some reproducable results in a less complicated way than in my first postings. 'select distinct' preforms on Orac

[SQL] Rule creation

2002-10-04 Thread Edoardo Causarano
Hello, I need help on this rule: CREATE RULE rule AS ON INSERT TO table WHERE (sum(new."field") > 10) DO INSTEAD NOTHING; Creation is ok but in inserting a row I get the following from pgaccessII number -2147467259 ERROR: fireRIRrules: failed to remove aggs from qual... Removing the INSTEAD from

Re: [SQL] [GENERAL] CURRENT_TIMESTAMP

2002-10-04 Thread Martijn van Oosterhout
On Sat, Sep 28, 2002 at 11:51:32PM -0400, Bruce Momjian wrote: > Martijn van Oosterhout wrote: > > Well, I'd rather it didn't change at all. IMHO it's a feature, not a bug. In > > any case, if it does get changed we'll have to go through the documentation > > and work out whether we mean current_t

Re: [SQL] [GENERAL] CURRENT_TIMESTAMP

2002-10-04 Thread Martijn van Oosterhout
On Sat, Sep 28, 2002 at 11:28:03PM -0400, Bruce Momjian wrote: > > OK, we have two db's returning statement start time, and Oracle 8 not > having CURRENT_TIMESTAMP. > > Have we agreed to make CURRENT_TIMESTAMP statement start, and now() > transaction start? Is this an open item or TODO item? W

Re: [SQL] Can Postgres cache a table in memory?

2002-10-04 Thread Achilleus Mantzios
On Thu, 3 Oct 2002, Kevin Traub wrote: > Hello all; > > I'm trying to speed up a query which returns the majority of a table so and > index isn't helpful. > I've got more than enough RAM to hold my table so, can anyone tell me if > there is there a way to force Postgres to cache a table in RAM? >

Re: [SQL] rows in order

2002-10-04 Thread Achilleus Mantzios
How do you plan to keep your tree represented?? There are quite a few options for this. Extensive talk has been made in this list. Also search the archives. Basically you can follow - nested trees (pure sql) aproach - Genealogical tree representation approach (either using text to represent the

Re: [SQL] please help with converting a view in oracle into postgresql readably code

2002-10-04 Thread Tom Lane
"Matthew Geddert" <[EMAIL PROTECTED]> writes: > create or replace view events_orders_states > as > select o.*, > o_states.order_state > from events_orders o, > (select > order_id, > decode (floor(avg (decode (reg_state, >'canceled', 0, >'waiting', 1, >

Re: [SQL] Can Postgres cache a table in memory?

2002-10-04 Thread Richard Huxton
On Thursday 03 Oct 2002 3:03 pm, Kevin Traub wrote: > Hello all; > > I'm trying to speed up a query which returns the majority of a table so and > index isn't helpful. > I've got more than enough RAM to hold my table so, can anyone tell me if > there is there a way to force Postgres to cache a tab

Re: [SQL] rows in order

2002-10-04 Thread Richard Huxton
On Monday 30 Sep 2002 7:29 pm, Camila Rocha wrote: > Hi! > > Is there a way to put in order the rows in a table? the problem is that i > want to keep a "tree" in the db, but the leaves must be ordered... does > someone have an idea? Do a search on "Joe Celko" and "tree" and you should find a clea

Re: [SQL] Can Postgres cache a table in memory?

2002-10-04 Thread Tom Lane
"Kevin Traub" <[EMAIL PROTECTED]> writes: > I'm trying to speed up a query which returns the majority of a table so and > index isn't helpful. > I've got more than enough RAM to hold my table so, can anyone tell me if > there is there a way to force Postgres to cache a table in RAM? There is no n

Re: [SQL] Rule creation

2002-10-04 Thread Tom Lane
Edoardo Causarano <[EMAIL PROTECTED]> writes: > Hello, I need help on this rule: > CREATE RULE rule AS ON INSERT TO table WHERE (sum(new."field") > 10) DO > INSTEAD NOTHING; > Creation is ok but in inserting a row I get the following from pgaccessII > number -2147467259 > ERROR: fireRIRrules: fail

Re: [SQL] How slow is distinct - 2nd

2002-10-04 Thread Tom Lane
"Michael Contzen" <[EMAIL PROTECTED]> writes: > [ select distinct takes a long time on 7+ million rows ] What do you have sort_mem set to? The default value is mighty small, and that would translate directly to poor performance in DISTINCT. Still though, the speed differential against Oracle is

Re: [SQL] How slow is distinct - 2nd

2002-10-04 Thread Bruno Wolff III
On Tue, Oct 01, 2002 at 14:18:50 +0200, Michael Contzen <[EMAIL PROTECTED]> wrote: > Here the table: > > mc=# \d egal > Table "public.egal" > Column | Type | Modifiers > +-+--- > i | integer | > > mc=# select count(*) from egal; > count > -

Re: [SQL] rows in order

2002-10-04 Thread Oleg Bartunov
On Fri, 4 Oct 2002, Achilleus Mantzios wrote: > > How do you plan to keep your tree represented?? > > There are quite a few options for this. > Extensive talk has been made in this list. > Also search the archives. > Basically you can follow > - nested trees (pure sql) aproach > - Genealogical tr

Re: [SQL] Can Postgres cache a table in memory?

2002-10-04 Thread Josh Berkus
Kevin, > I'm trying to speed up a query which returns the majority of a table > so and > index isn't helpful. > I've got more than enough RAM to hold my table so, can anyone tell me > if > there is there a way to force Postgres to cache a table in RAM? Please join the PGSQL-PERFORMANCE mailing l

Re: [SQL] rows in order

2002-10-04 Thread Joe Conway
Camila Rocha wrote: > Is there a way to put in order the rows in a table? the problem is that i w= > ant to keep a "tree" in the db, but the leaves must be ordered... > does someone have an idea? If you don't mind trying 7.3 beta, there is a function called connectby() in contrib/tablefunc. It w

[SQL] Self Join?

2002-10-04 Thread bens_nospam
I'm having difficulty coming up with the right join to get my results. I'm using PostgreSQL 7.2.x I'm looking for a "most likely match" result. Assume you have a table with two fields, field 1 is a serial key (unique) and field 2 is varchar. Assume that you have the following entries in the

[SQL] enforcing with unique indexes..

2002-10-04 Thread Rajesh Kumar Mallah.
Hi , can anyone tell me how can i enforce below in a table. I want that no more that one distinct userid exists for a given group_id in the table. ie i want 1 to 1 mapping between group_id and userid so that , there shud not be a single group_id having more that one kind of userid. SELECT gr

Re: [SQL] enforcing with unique indexes..

2002-10-04 Thread Rajesh Kumar Mallah.
Sorry Bhuvan it wont work, COMPOSITE UNIQUE INDEX will prevent entry of rows like (group_id,user_id) 1 1 1 1 what i want to prevent is this: 1 1 1 2 did you notice the distinct inside the count? regds mallah. On Saturday 05 October 2002 12:36, Bhuvan A wrote: > > SELECT group_id from