[SQL] please help with converting a view in oracle into postgresql readably code
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 the varchar variables present in reg_state, into numbers to represent the various possibilities). Then, once it has found the average number for all items in the table events_registrations with the same order_id number it fills in a human readable word. Thanks for any help in letting me either know how to create this view, or to tell me how to convert the variables in the varchar field reg_state into the numbers I want them to represent which I will be able to manipulate with things such as floor() and avg(). Matthew Geddert = 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, 'pending', 2, 'shipped', 3, 0))), 0, 'canceled', 1, 'incomplete', 2, 'incomplete', 3, 'fulfilled', 'void') as order_state from events_registrations group by order_id) o_states where o_states.order_id = o.order_id; ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] rows in order
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?
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 Traub ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] How slow is distinct - 2nd
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 Oracle about 30 times faster than in Postgres. Well, only 'select distinct' is slow. All other parts of our application using Postgres perform quite well on Postgres (with really big amounts of data). (All tests on Postgres 7.3b1 and Oracle 9.0.1.3.0. Hardware: 4 * 1800 MHz Xeon, 3 * 100GB IDE Software-Raid0) Suse-Linux, 2.4.18 SMP-kernel) Here the table: mc=# \d egal Table "public.egal" Column | Type | Modifiers +-+--- i | integer | mc=# select count(*) from egal; count - 7227744 (1 row) mc=# select count(distinct i) from egal; count --- 67 (1 row) (The integers have values between 0 and 99 are an extract of our warehouse and nearly distributed randomly) The last query - count(distinct) - takes 1m30s while oracle needs 0m7s for the same query and data. Getting the distinct rows and measuring the time results to time echo "select distinct i from egal;"|psql >/dev/null real4m52.108s user0m0.000s sys 0m0.010s Here I don't understand the difference in performance between "select distinct i" and "select count(distinct i)" - Oracle takes constantly 7s for each query: time echo "select distinct i from egal;"|sqlplus / >/dev/null real0m6.979s user0m0.020s sys 0m0.030s In the first case (count(distinct)) postgres produces a temporary file of 86 MB in pgsql_tmp, in the second case (select distinct) the temp-file increases to 260 MB. (this is even larger than the table size of egal which is 232 MB) I think the planner has not many choices for this query so it results to mc=# explain select distinct (i) from egal; QUERY PLAN --- Unique (cost=1292118.29..1328257.01 rows=722774 width=4) -> Sort (cost=1292118.29..1310187.65 rows=7227744 width=4) Sort Key: i -> Seq Scan on egal (cost=0.00..104118.44 rows=7227744 width=4) (4 rows) Which looks similar to oracle's plan: QPLAN --- SORT UNIQUE TABLE ACCESS FULL EGAL Our problem is that things getting worse when the table size increases, as described in my first mails. (Especially when the temp-file is split into parts) Reorganizing/normalizing the data is no solultion, because our application is just for analyzing the data. I tried out the trigger-idea: inserting a value into a table with an unique key only if still does't exists, it works, but .. select distinct is a much faster solution. An other way I tried is an PL/TCL function which stores all different values into a assoziative array in memory, by far the fastest solution - but returning the different values makes problems to me (I shouldn't say, but I put it into a file and "copy" it back). And it's not very nice - not as nice as "select distinct" :) Kind regards, Michael Contzen <> ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Rule creation
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 the rule inhibits the error but makes the rule pointless as the statement execution proceeds to the insertion. I need a rule that denies insertions if the total of the rows with an active attribute maxes out (guess what, a vacancy set). I'm confused, any help? -- 9:33pm up 1:00, 1 user, load average: 0.87, 0.47, 0.21 ---(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] [GENERAL] CURRENT_TIMESTAMP
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_timestamp or now(). I think most people > > actually want now(). > > Well, I think we have to offer statement start time somewhere, and it > seems the standard probably requires that. Two other databases do it > that way. Oracle doesn't have CURRENT_TIMESTAMP in 8.X. Can anyone > test on 9.X? Hmm, well having a statement start time could be conceivably useful. > > Fortunatly where I work we only use now() so it won't really matter too > > much. Is there a compelling reason to change? > > Yes, it will split now() and CURRENT_TIMESTAMP. I personally would be > happy with STATEMENT_TIMESTAMP, but because the standard requires it we > may just have to fix CURRENT_TIMESTAMP. Well, my vote would be for STATEMENT_TIMESTAMP. Is there really no other database that does it the way we do? Perhaps it could be matched with a TRANSACTION_TIMESTAMP and we can sort out CURRENT_TIMESTAMP some other way. -- Martijn van Oosterhout <[EMAIL PROTECTED]> http://svana.org/kleptog/ > There are 10 kinds of people in the world, those that can do binary > arithmetic and those that can't. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] [GENERAL] CURRENT_TIMESTAMP
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? 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_timestamp or now(). I think most people actually want now(). Fortunatly where I work we only use now() so it won't really matter too much. Is there a compelling reason to change? > --- > > Manfred Koizar wrote: > > On Tue, 24 Sep 2002 17:56:51 -0400 (EDT), Bruce Momjian > > <[EMAIL PROTECTED]> wrote: > > >Can you run a test: > > > > > > BEGIN; > > > SELECT CURRENT_TIMESTAMP; > > > wait 5 seconds > > > SELECT CURRENT_TIMESTAMP; > > > > > >Are the two times the same? > > > > MS SQL 7: > > begin transaction > > insert into tst values (CURRENT_TIMESTAMP) > > -- wait > > insert into tst values (CURRENT_TIMESTAMP) > > commit > > select * from tst > > > > t > > --- > > 2002-09-24 09:49:58.777 > > 2002-09-24 09:50:14.100 > > > > Interbase 6: > > SQL> select current_timestamp from rdb$database; > > > > = > > 2002-09-24 22:30:13. > > > > SQL> select current_timestamp from rdb$database; > > > > = > > 2002-09-24 22:30:18. > > > > SQL> commit; > > > > Servus > > Manfred > > > > -- > Bruce Momjian| http://candle.pha.pa.us > [EMAIL PROTECTED] | (610) 359-1001 > + If your life is a hard drive, | 13 Roberts Road > + Christ can be your backup.| Newtown Square, Pennsylvania 19073 > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster -- Martijn van Oosterhout <[EMAIL PROTECTED]> http://svana.org/kleptog/ > There are 10 kinds of people in the world, those that can do binary > arithmetic and those that can't. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Can Postgres cache a table in memory?
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? > > Any help would be appreciated. You need to tune first of all shared_buffers. Change the postgres.conf file usual in $PGDATA directory. Bare in mind that one buffer holds 8k of data. > > Thanks; > -Kevin Traub > > > > ---(end of broadcast)--- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) > == Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt Nikis 4, Glyfada Athens 16610 Greece tel:+30-10-8981112 fax:+30-10-8981877 email: [EMAIL PROTECTED] [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] rows in order
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 path to the parent (pure sql), or arrays) - The contrib/tree implementation == Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt Nikis 4, Glyfada Athens 16610 Greece tel:+30-10-8981112 fax:+30-10-8981877 email: [EMAIL PROTECTED] [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] please help with converting a view in oracle into postgresql readably code
"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, >'pending', 2, >'shipped', 3, >0))), > 0, 'canceled', > 1, 'incomplete', > 2, 'incomplete', > 3, 'fulfilled', > 'void') as order_state > from events_registrations > group by order_id) o_states > where o_states.order_id = o.order_id; DECODE() is an Oracle-ism. You can use the SQL-standard CASE construct instead. See http://www.ca.postgresql.org/users-lounge/docs/7.2/postgres/functions-conditional.html Other than the DECODE() calls this should run fine in PG. 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] Can Postgres cache a table in memory?
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 table in RAM? You're probably just as well off if you let your OS deal with caching the underlying file. What figures are you getting (use the "free" command if in linux). It might be that you've given Postgresql *too* much memory, and that's interfering with your OS. Could you provide brief hardware specs, the size of the table concerned, time taken? -- Richard Huxton ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] rows in order
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 clean way to model trees in SQL. -- Richard Huxton ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Can Postgres cache a table in memory?
"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 need to do anything; if you're hitting the table a lot, it should all migrate into kernel disk buffers. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Rule creation
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: failed to remove aggs from qual... This is a known bug, but the fix will be to reject such rules entirely. An aggregate in a rule's WHERE clause is not sensible --- what are you aggregating over? > I need a rule that denies insertions if the total of the rows with an active > attribute maxes out (guess what, a vacancy set). I think a rule is the wrong way to approach this anyhow. What would probably make more sense is an AFTER INSERT OR UPDATE trigger that runs a SUM() computation on the table and throws an error if it doesn't like the result. 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] How slow is distinct - 2nd
"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 surprising, given that they don't seem to be using a fundamentally different implementation. Time to get out the profiler ... regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] How slow is distinct - 2nd
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 > - > 7227744 > (1 row) > > mc=# select count(distinct i) from egal; > count > --- > 67 > (1 row) This suggests that the best way to do this is with a hash instead of a sort. If you have lots of memory you might try increasing the sort memory size. ---(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] rows in order
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 tree representation approach > (either using text to represent the path to the parent (pure sql), or > arrays) > - The contrib/tree implementation contrib/ltree ! > > == > Achilleus Mantzios > S/W Engineer > IT dept > Dynacom Tankers Mngmt > Nikis 4, Glyfada > Athens 16610 > Greece > tel:+30-10-8981112 > fax:+30-10-8981877 > email: [EMAIL PROTECTED] > [EMAIL PROTECTED] > > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org > Regards, Oleg _ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Can Postgres cache a table in memory?
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 list and post this question there: [EMAIL PROTECTED] message: "subscribe" -Josh Berkus ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] rows in order
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 works like this:
CREATE TABLE connectby_tree(keyid text, parent_keyid text);
INSERT INTO connectby_tree VALUES('row1',NULL);
INSERT INTO connectby_tree VALUES('row2','row1');
INSERT INTO connectby_tree VALUES('row3','row1');
INSERT INTO connectby_tree VALUES('row4','row2');
INSERT INTO connectby_tree VALUES('row5','row2');
INSERT INTO connectby_tree VALUES('row6','row4');
INSERT INTO connectby_tree VALUES('row7','row3');
INSERT INTO connectby_tree VALUES('row8','row6');
INSERT INTO connectby_tree VALUES('row9','row5');
SELECT * FROM
connectby('connectby_tree', 'keyid', 'parent_keyid', 'row2', 0, '~')
AS t(keyid text, parent_keyid text, level int, branch text);
keyid | parent_keyid | level | branch
---+--+---+-
row2 | | 0 | row2
row4 | row2 | 1 | row2~row4
row6 | row4 | 2 | row2~row4~row6
row8 | row6 | 3 | row2~row4~row6~row8
row5 | row2 | 1 | row2~row5
row9 | row5 | 2 | row2~row5~row9
(6 rows)
This allows completely dynamically generated trees.
There is also a contrib/ltree, which I believe creates a persistent structure
for the tree information, and gives you tools to manipulate it (but I have
never used it, so my discription may not be completely accurate).
HTH,
Joe
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
[SQL] Self Join?
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 table:
recordid val
1, 'a'
2, 'b'
3, 'ab'
And I want to match the strings "a" and "b", but not necessarily "ab", and disregard
an additional "c", and organize the result so that the records that best match are at
the top. Sample output might be:
count recordid
2 3
1 1
1 2
Record #3, containing both "a" and "b" has two count, records 1 and 2 having only one
of "a" or "b" have a count of 1.
The closest that I've come so far is from a query like
select id from table where lower(val) like lower('%a%') UNION ALL select id from table
where lower(val) like lower('%b%') UNION ALL select id from table where lower(val)
like lower(%c%');
What this gives me is
id
1
3
2
3
which is somewhat close, but then requires me to loop thru a potentially large number
of results to get the requested output.
Anybody else up to this one?
-Ben
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] enforcing with unique indexes..
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 group_id from eyp_listing group by group_id having count(distinct userid) > 1 ; always returns empty. can it be done with some sort of UNIQUE INDEX? Regds MAllah. -- Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)6152172 (221) (L) ,9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace. ---(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] enforcing with unique indexes..
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 eyp_listing group by group_id having > > count(distinct userid) > 1 ; > > > > always returns empty. > > > > can it be done with some sort of UNIQUE INDEX? > > Yes it can be done using UNIQUE INDEX. Create a composite unique index on > these 2 fields and it will do the rest. For more details, try > > $ \h CREATE INDEX > > regards, > bhuvaneswaran -- Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)6152172 (221) (L) ,9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace. ---(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
