Re: [GENERAL] greatest/least semantics different between oracle and postgres
Hello, I have not Oracle, so I cannot test it, but PostgreSQL implementation respect Oracle: http://archives.postgresql.org/pgsql-patches/2005-06/msg00431.php Regards Pavel Stehule 2007/6/30, Bruno Wolff III [EMAIL PROTECTED]: The following is just FYI. I was recently doing some stuff with greatest() on oracle (9.2.0.8.0) and noticed that it returned null if ANY of the arguments were null. Out of curiosity I checked postgres' definition of that function and found that it returns null only if ALL of the arguments are null. ---(end of broadcast)--- TIP 1: 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: don't forget to increase your free space map settings
Re: [GENERAL] How does one impolement lists, or arrays, pin pl/pgsql ?
Hello
PostgreSQL supports modifyable arrays. You have to respect some
specific (depend on version of postgresql). Array have to be
initialised (filled) for all pg versions older than 8.2.
create or replace function foo()
returns int as $$
declare a int[] = '{0,0,0,0,0,0,0,0,0,0}'; -- inicialisation need for
versions 8.2.
begin a[4] := 10; a[8] := 10;
raise notice '%', a;
return a[4];
end$$ language plpgsql;
Regards
Pavel Stehule
2007/6/29, Gauthier, Dave [EMAIL PROTECTED]:
How does one implement a simple, general purpose, assignable array (or list)
in pl/pgsql? From what I've found/read, it appears that you can declare
static, read-only arrays. I'm guessing (and please correct if I'm wrong),
PG does not support modifyable arrays. Rather, one might consider defining
a temporary table and using sql to insert/select to/from it. True?
---(end of broadcast)---
TIP 1: 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
[GENERAL] stem tsearch2, want different stemmed words
Hi!
bond= SELECT to_tsvector('default','animation animal');
to_tsvector
-
'anim':1,2
(1 row)
bond=
Sorry for a silly question, I wonder, how do I override this? I would
want different stemmed words for these.
Best regards,
Marcus
---(end of broadcast)---
TIP 1: 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: [GENERAL] Looking for help regarding getting the latest inserted sequence value.
Mavinakuli, Prasanna (STSD) wrote:
Hello All,
We are looking for your help.The scenarion which we need to address
is,There are 2 threads and both of them are in separate transction and
insert the value to a same table and also sequence number field gets
incremented automotically for each of them.The problem we are facing
is,We will need to get back the appropriate id inserted for that
particualr record as it is used in some other places.
Right now we are doing it in 2 steps.inserting the record to table.And
getting the max(id) from the table.Now the problem is assume there is
another thread also does the insertion and commits that transction both
of the thread return the same id which is not desirable in our case.
It would be really very much helpful to know the form of a query which
inserts record and also returns the latest inserted ID for that record
in a single query.
If you're on 8.2 the easiest way is to use INSERT RETURNING. For example:
INSERT INTO t (somefield) VALUES ('somevalue') RETURNING pkey
with pkey being the SERIAL field.
You can also do it with currval() on the sequence, but that requires two
queries.
//Magnus
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [GENERAL] installing pljava on windows xp
Pouria wrote: Hi, When I try to install pljava 1.3 on postgresql 8.2 (or 8.1) I get an error stating that it cannot load pljava.dll from a location specificed in the config file (while the dll is clearly there). I have followed the postgredql and pljava manual installation instructions exactly with no luck. This error is listed as fixed on the pljava wiki, yet i'm still experiencing it. Any ideas? Most likely it's a missing dependency. Try using the depends tool (from the windows support tools) to find out why it's failing. //Magnus ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] SQL problem..
Bauhaus [EMAIL PROTECTED] schrieb: Hello, I'm an Access/SQL novice and I have an sql problem: I have the following table Price: FuelID PriceDate Price LPG1/05/2007 0,2 LPG13/05/2007 0,21 SPS 2/05/2007 1,1 SPS 15/05/2007 1,08 And I have to make the following query: FuelID PriceDate_from PriceDate_To Price LPG1/05/2007 13/05/2007 0,2 SPS 2/05/2007 15/05/20071,1 LPG13/05/2007 0,21 SPS 15/05/2007 1,08 How can I solve this ? There are different solutions, one of them: write a function like this: create or replace function price_list(OUT _id text, OUT _date_from date, out _date_to date, OUT _price numeric(10,2)) returns setof record as $$ declare _row record; _old date; _old_id text; begin _old := NULL; _old_id := NULL; for _row in select * from price order by fuel_id, price_date desc loop if _old_id != _row.fuel_id then _date_to := NULL; else _date_to := _old; end if; _old_id := _row.fuel_id; _id := _row.fuel_id; _date_from := _row.price_date; -- _date_to := _old; _price := _row.price; _old := _row.price_date; return next ; end loop; end; $$ language plpgsql; Test: test=*# \d price Table public.price Column | Type | Modifiers +---+--- fuel_id| text | price_date | date | price | numeric(10,2) | test=*# select * from price ; fuel_id | price_date | price -++--- LPG | 2007-05-01 | 0.20 LPG | 2007-05-13 | 0.21 SPS | 2007-05-02 | 1.10 SPS | 2007-05-15 | 1.08 (4 rows) test=*# select * from price_list() order by _id, _date_from asc; _id | _date_from | _date_to | _price -+++ LPG | 2007-05-01 | 2007-05-13 | 0.20 LPG | 2007-05-13 || 0.21 SPS | 2007-05-02 | 2007-05-15 | 1.10 SPS | 2007-05-15 || 1.08 (4 rows) Other solution: Alter your table and add a new column for the end-valid-date and write a trigger. This trigger updates your table for every new inserted record. (insert the new inserted date_from as date_to into the last record) Finaly a link for you: http://www.rueping.info/doc/Andreas%20Rping%20--%202D%20History.pdf Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) If I was god, I would recompile penguin with --enable-fly.(unknow) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889° ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] postgressqlnot support inwindows 2000
siva prakash wrote: if i run the setup choose language then go to next button it shows error *Failed to create process: 2!* Please don't remove the mailinglist from the CC list, so others can learn from the answers. The error you get indicates that your windows installation is broken. At that point it tries to execute msiexec which is a part of windows installer and a core piece of windows that's not working. You need to make sure that it works properly before you can install PostgreSQL. //Magnus ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Possible bug (or I don't understand how foreign keys should work with partitions)
On Fri, 22 Jun 2007 18:23:44 -0300
Daniel van Ham Concrete [EMAIL PROTECTED] wrote:
# Insert four rows in regs (rID will go from 1 to 4):
insert into regs (name, number) values ('Daniel', 4);
insert into regs (name, number) values ('Daniel', 14);
insert into regs (name, number) values ('Daniel', 5);
insert into regs (name, number) values ('Daniel', 15);
# Insert a 'invalid' row in regsemail
insert into regsemail (fk_regs_id, email) values (6, '[EMAIL PROTECTED]');
# END!
I should get an error saying something like ...violates foreign key
constraint... but I'm not getting anything. That's the bug. If I don't have
the partitions them I get the error message (as I think I should).
The problem I'm trying to solve is: I'll have a 1.8 billion rows table
(regs) and another one having at least one row to each row from the first
one. The solution is very simple: partitions. The 1.8 billion rows is
distributed uniformly in the days of the year, so I'll create one partition
for each day. But I have to do something similar with the second table as
well otherwise I wouldn't win much if I had to do a JOIN. I was testing how
foreign keys would work in this case and ran into this.
Is this really a bug? If not, what am I doing wrong please?
Best regards,
Daniel
Hello,
I could make child tables inherit an external reference
by the following queries. Actually, There appears to be referring
to foreign keys.
test=# INSERT INTO regsemail (rID, email) VALUES(6,'[EMAIL PROTECTED]');
ERROR: insert or update on table regsemail_00 violates foreign key constraint
regsemail_00_rid_fkey
--
Regard,
Masaru Sugawara
-- First, make regs_xx inherit the primary key constraint on regs.
CREATE TABLE regs (rID serial PRIMARY KEY, name text, number int);
CREATE TABLE regs_00 (CHECK (number = 00 AND number 10),
PRIMARY KEY (rID)) INHERITS (regs);
CREATE OR REPLACE RULE insert_regs_00 AS ON INSERT TO regs
WHERE (number = 00 AND number 10)
DO INSTEAD INSERT INTO regs_00
VALUES (NEW.rID, NEW.name, NEW.number);
CREATE TABLE regs_10 (CHECK (number = 10 AND number 20),
PRIMARY KEY (rID)) INHERITS (regs);
CREATE OR REPLACE RULE insert_regs_10 AS ON INSERT TO regs
WHERE (number = 10 AND number 20 )
DO INSTEAD INSERT INTO regs_10
VALUES (NEW.rID, NEW.name, NEW.number);
-- Second, make regsemail_xx inherit the foreign key constraint
-- on regsemail.
CREATE TABLE regsemail (dID serial PRIMARY KEY, rID integer, email text,
FOREIGN KEY (rID) REFERENCES regs(rID)
ON DELETE CASCADE);
CREATE TABLE regsemail_00 (CHECK (rID = 0 AND rID 10),
FOREIGN KEY (rID) REFERENCES regs_00(rID)
ON DELETE CASCADE) INHERITS(regsemail);
CREATE OR REPLACE RULE insert_regsemail_00 AS ON INSERT TO regsemail
WHERE (rID = 0 AND rID 10)
DO INSTEAD INSERT INTO regsemail_00
VALUES (NEW.dID, NEW.rID, NEW.email);
CREATE TABLE regsemail_10 (CHECK (rID = 10 AND rID 20 ),
FOREIGN KEY (rID) REFERENCES regs_10(rID)
ON DELETE CASCADE) INHERITS (regsemail);
CREATE OR REPLACE RULE insert_regsemail_10 AS ON INSERT TO regsemail
WHERE (rID = 10 AND rID 20)
DO INSTEAD INSERT INTO regsemail_10
VALUES (NEW.dID, NEW.rID, NEW.email);
-- Insert four rows in regs
INSERT INTO regs (name, number) VALUES ('Daniel', 4);
INSERT INTO regs (name, number) VALUES ('Daniel', 14);
INSERT INTO regs (name, number) VALUES ('Daniel', 5);
INSERT INTO regs (name, number) VALUES ('Daniel', 15);
SELECT * FROM regs_00;
SELECT * FROM regs_10;
SELECT r.*, p.relname AS inherited table FROM regs r, pg_class p
WHERE r.tableoid = p.oid;
-- Test for foreign key constraint
INSERT INTO regsemail (rID, email) VALUES(1,'[EMAIL PROTECTED]');
INSERT INTO regsemail (rID, email) VALUES(3,'[EMAIL PROTECTED]');
INSERT INTO regsemail (rID, email) VALUES(6,'[EMAIL PROTECTED]');
SELECT * FROM regsemail_00;
SELECT * FROM regsemail_10;
SELECT r.*, p.relname AS inherited table FROM regsemail r, pg_class p
WHERE r.tableoid = p.oid;
-- Test for ON DELETE CASCADE
DELETE FROM regs WHERE rID = 3;
SELECT * FROM regs_00;
SELECT * FROM regs_10;
SELECT r.*, p.relname AS inherited table FROM regs r, pg_class p
WHERE r.tableoid = p.oid;
SELECT * FROM regsemail_00;
SELECT * FROM regsemail_10;
SELECT r.*, p.relname AS inherited table FROM regsemail r, pg_class p
WHERE r.tableoid = p.oid;
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] greatest/least semantics different between oracle and postgres
Bruno Wolff III [EMAIL PROTECTED] writes: Also what value should I have used in a coalesce to guaranty still getting the maximum? I would expect coalesce(coalesce(greatest(a,b),a),b) to do what you want. It's not terribly legible though and if a and b are subselects I would worry a little about the optimizer rerunning them unnecessarily. Perhaps coalesce(greatest(a,b), coalesce(a,b)) is more legible? -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 1: 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: [GENERAL] greatest/least semantics different between oracle and postgres
Tom Lane [EMAIL PROTECTED] writes: GREATEST/LEAST aren't in the spec, so there's not much help there. Except ... if they ever do get added to the spec, what do you think the spec will say? The odds it'd contradict Oracle seem about nil. Fwiw even in the min/max/sum case the spec is moving away from having aggregates ignore NULL values. You now get a warning in Oracle if your aggregate includes any NULL inputs. Actually I think it's not exactly a warning but a weird kind of non-fatal error. You still get your result set but the driver treats it as an error which has to be explicitly handled to see the results. I'm not entirely clear on what's going on though. I know that there was some version of their sql command-line tool which *didn't* handle it and therefore treated it as a fatal error and that pissed off a lot of people. I think it now prints the warning and the result set. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] postgressqlnot support inwindows 2000
--- Original Message --- From: Magnus Hagander [EMAIL PROTECTED] To: siva prakash [EMAIL PROTECTED], 'PostgreSQL' pgsql-general@postgresql.org Sent: 30/06/07, 13:33:04 Subject: Re: [GENERAL] postgressqlnot support inwindows 2000 siva prakash wrote: if i run the setup choose language then go to next button it shows error *Failed to create process: 2!* Please don't remove the mailinglist from the CC list, so others can learn from the answers. The error you get indicates that your windows installation is broken. At that point it tries to execute msiexec which is a part of windows installer and a core piece of windows that's not working. You need to make sure that it works properly before you can install PostgreSQL. Unless I'm misreading it errors when the Next Button is clicked on the language dialog, which means msiexec has already run once. Siva; did you extract both msi files from the zip file before running the installer? Regards, Dave ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] greatest/least semantics different between oracle and postgres
Gregory Stark wrote: Tom Lane [EMAIL PROTECTED] writes: GREATEST/LEAST aren't in the spec, so there's not much help there. Except ... if they ever do get added to the spec, what do you think the spec will say? The odds it'd contradict Oracle seem about nil. Fwiw even in the min/max/sum case the spec is moving away from having aggregates ignore NULL values. You now get a warning in Oracle if your aggregate includes any NULL inputs. How does Oracle's new behavior relate to the standard moving? -- Bruce Momjian [EMAIL PROTECTED] http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] stem tsearch2, want different stemmed words
On Sat, 30 Jun 2007, Marcus Engene wrote:
Hi!
bond= SELECT to_tsvector('default','animation animal');
to_tsvector
-
'anim':1,2
(1 row)
bond=
Sorry for a silly question, I wonder, how do I override this? I would want
different stemmed words for these.
create synonym dictionary. Read about this
http://www.sai.msu.su/~megera/wiki/Tsearch_V2_Notes
Best regards,
Marcus
---(end of broadcast)---
TIP 1: 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
Regards,
Oleg
_
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83
---(end of broadcast)---
TIP 1: 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: [GENERAL] greatest/least semantics different between oracle and postgres
Gregory Stark [EMAIL PROTECTED] writes: Bruno Wolff III [EMAIL PROTECTED] writes: Also what value should I have used in a coalesce to guaranty still getting the maximum? I would expect coalesce(coalesce(greatest(a,b),a),b) to do what you want. It's not terribly legible though and if a and b are subselects I would worry a little about the optimizer rerunning them unnecessarily. That does not work correctly for volatile functions, and it does not scale to more than two inputs either -- you'd get the first nonnull not the largest one. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] greatest/least semantics different between oracle and postgres
Gregory Stark [EMAIL PROTECTED] writes: Fwiw even in the min/max/sum case the spec is moving away from having aggregates ignore NULL values. You now get a warning in Oracle if your aggregate includes any NULL inputs. I don't think there's any moving involved; as far back as SQL92 the definition of aggregates (except COUNT) said b) Otherwise, let TX be the single-column table that is the result of applying the value expression to each row of T and eliminating null values. If one or more null values are eliminated, then a completion condition is raised: warning- null value eliminated in set function. We pretty much ignore the spec's concept of non-error completion conditions, but it sounds like Oracle tries to support it. Anyway, there's no doubt that we can point to the behavior of MAX/MIN as defense for what we made GREATEST/LEAST do, so I'm inclined to leave their behavior alone, at least until such time as they're actually standardized. But a note in the manual pointing out the difference from Oracle seems in order. BTW, it seems that mysql follows Oracle on this: mysql select greatest(1,4,8); +-+ | greatest(1,4,8) | +-+ | 8 | +-+ 1 row in set (0.00 sec) mysql select greatest(1,4,null); ++ | greatest(1,4,null) | ++ | NULL | ++ 1 row in set (0.00 sec) and if you want a laugh: mysql select greatest (1,4,8); ERROR 1305 (42000): FUNCTION test.greatest does not exist regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] greatest/least semantics different between oracle and postgres
Bruce Momjian [EMAIL PROTECTED] writes: Fwiw even in the min/max/sum case the spec is moving away from having aggregates ignore NULL values. You now get a warning in Oracle if your aggregate includes any NULL inputs. How does Oracle's new behavior relate to the standard moving? Sorry I noticed that editing error only after I sent it. I should have changed that to say Oracle was moving in that direction. There's nothing of the sort in SQL2003 that I can find. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] greatest/least semantics different between oracle and postgres
Tom Lane [EMAIL PROTECTED] writes: Gregory Stark [EMAIL PROTECTED] writes: Bruno Wolff III [EMAIL PROTECTED] writes: Also what value should I have used in a coalesce to guaranty still getting the maximum? I would expect coalesce(coalesce(greatest(a,b),a),b) to do what you want. It's not terribly legible though and if a and b are subselects I would worry a little about the optimizer rerunning them unnecessarily. That does not work correctly for volatile functions, and it does not scale to more than two inputs either -- you'd get the first nonnull not the largest one. Both true. There is another option too if you have a minimum value below which you know no values will exist: SELECT nullif(greatest(coalesce(a,-1), coalesce(b,-1), coalesce(c,-1)), -1) Does Oracle even have nullif() these days? If not you would have to use decode() but I think it suffers from the same problem of repeated evaluation. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] greatest/least semantics different between oracle and postgres
Tom Lane wrote: Gregory Stark [EMAIL PROTECTED] writes: Fwiw even in the min/max/sum case the spec is moving away from having aggregates ignore NULL values. You now get a warning in Oracle if your aggregate includes any NULL inputs. I don't think there's any moving involved; as far back as SQL92 the definition of aggregates (except COUNT) said b) Otherwise, let TX be the single-column table that is the result of applying the value expression to each row of T and eliminating null values. If one or more null values are eliminated, then a completion condition is raised: warning- null value eliminated in set function. We pretty much ignore the spec's concept of non-error completion conditions, but it sounds like Oracle tries to support it. Anyway, there's no doubt that we can point to the behavior of MAX/MIN as defense for what we made GREATEST/LEAST do, so I'm inclined to leave their behavior alone, at least until such time as they're actually standardized. But a note in the manual pointing out the difference from Oracle seems in order. Agreed that we are good by following min/max. Not sure about a mention in the docs that we are different from Oracle helps. Do we mention other differences? I see us doing that only for PL/Psql. -- Bruce Momjian [EMAIL PROTECTED] http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] greatest/least semantics different between oracle and postgres
Bruce Momjian [EMAIL PROTECTED] writes: Tom Lane wrote: Anyway, there's no doubt that we can point to the behavior of MAX/MIN as defense for what we made GREATEST/LEAST do, so I'm inclined to leave their behavior alone, at least until such time as they're actually standardized. But a note in the manual pointing out the difference from Oracle seems in order. Agreed that we are good by following min/max. Not sure about a mention in the docs that we are different from Oracle helps. Do we mention other differences? I see us doing that only for PL/Psql. We tend not to mention Oracle by name, but there are various places saying that we do X while some other databases do Y. In view of the mysql behavior I think I'd use that same wording here. regards, tom lane ---(end of broadcast)--- TIP 1: 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: [GENERAL] greatest/least semantics different between oracle and postgres
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: Tom Lane wrote: Anyway, there's no doubt that we can point to the behavior of MAX/MIN as defense for what we made GREATEST/LEAST do, so I'm inclined to leave their behavior alone, at least until such time as they're actually standardized. But a note in the manual pointing out the difference from Oracle seems in order. Agreed that we are good by following min/max. Not sure about a mention in the docs that we are different from Oracle helps. Do we mention other differences? I see us doing that only for PL/Psql. We tend not to mention Oracle by name, but there are various places saying that we do X while some other databases do Y. In view of the mysql behavior I think I'd use that same wording here. OK, I like the generic approach. -- Bruce Momjian [EMAIL PROTECTED] http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] greatest/least semantics different between oracle and postgres
Tom Lane wrote: Anyway, there's no doubt that we can point to the behavior of MAX/MIN as defense for what we made GREATEST/LEAST do, so I'm inclined to leave their behavior alone, at least until such time as they're actually standardized. I don't think I buy this - MIN and MAX are aggregates, GREATEST is just a function, yes? There would seem to be a very strong analogy with SUM and the addition function, yet 2 + NULL is NULL, while SUM ignores NULLs. (Not in front of a console, sorry if I'm mistaken.) - John Burger MITRE ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] greatest/least semantics different between oracle and postgres
On Sat, Jun 30, 2007 at 09:29:23 +0200, Pavel Stehule [EMAIL PROTECTED] wrote: Hello, I have not Oracle, so I cannot test it, but PostgreSQL implementation respect Oracle: http://archives.postgresql.org/pgsql-patches/2005-06/msg00431.php Maybe that reference was for an earlier version of Oracle and the definition changed at some point? I only have access to version 9 and greatest and lest are strict there. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] greatest/least semantics different between oracle and postgres
We pretty much ignore the spec's concept of non-error completion conditions, but it sounds like Oracle tries to support it. Anyway, there's no doubt that we can point to the behavior of MAX/MIN as defense for what we made GREATEST/LEAST do, so I'm inclined to leave their behavior alone, at least until such time as they're actually standardized. But a note in the manual pointing out the difference from Oracle seems in order. BTW, it seems that mysql follows Oracle on this: mysql select greatest(1,4,8); +-+ | greatest(1,4,8) | +-+ | 8 | +-+ 1 row in set (0.00 sec) mysql select greatest(1,4,null); ++ | greatest(1,4,null) | ++ | NULL | ++ 1 row in set (0.00 sec) and if you want a laugh: mysql select greatest (1,4,8); ERROR 1305 (42000): FUNCTION test.greatest does not exist regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq Hello some notes about it http://bugs.mysql.com/bug.php?id=12791 http://bugs.mysql.com/bug.php?id=15610 Regards Pavel ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] greatest/least semantics different between oracle and postgres
Maybe that reference was for an earlier version of Oracle and the definition changed at some point? I only have access to version 9 and greatest and lest are strict there. I am installing OracleXE and I'll test it. Pavel ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] greatest/least semantics different between oracle and postgres
At risk of putting my foot in my mouth again, greatest() returns null if one or more expressions are null for Oracle enterprise 9.2.0.7 and 10.2.0.3. The docs for greatest() don't talk of NULL: SQL select greatest(1,2,null,3) from dual; GREATEST(1,2,NULL,3) SQL select greatest(1,2,3) from dual; GREATEST(1,2,3) --- 3 SQL select version from v$instance; VERSION --- 9.2.0.7.0 SQL select greatest(1,2,null,3) from dual; GREATEST(1,2,NULL,3) SQL select greatest(1,2,3) from dual; GREATEST(1,2,3) --- 3 SQL select version from v$instance; VERSION --- 10.2.0.3.0 -Original Message- From: [EMAIL PROTECTED] [mailto:pgsql-general- [EMAIL PROTECTED] On Behalf Of Pavel Stehule Sent: Saturday, June 30, 2007 10:37 AM To: Bruno Wolff III; Pavel Stehule; pgsql-general@postgresql.org Subject: Re: [GENERAL] greatest/least semantics different between oracle and postgres Maybe that reference was for an earlier version of Oracle and the definition changed at some point? I only have access to version 9 and greatest and lest are strict there. I am installing OracleXE and I'll test it. Pavel ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] greatest/least semantics different between oracle and postgres
-Original Message- From: [EMAIL PROTECTED] [mailto:pgsql-general- [EMAIL PROTECTED] On Behalf Of Pavel Stehule Sent: Saturday, June 30, 2007 10:37 AM To: Bruno Wolff III; Pavel Stehule; pgsql-general@postgresql.org Subject: Re: [GENERAL] greatest/least semantics different between oracle and postgres Maybe that reference was for an earlier version of Oracle and the definition changed at some point? I only have access to version 9 and greatest and lest are strict there. I am installing OracleXE and I'll test it. Pavel At risk of putting my foot in my mouth again, greatest() returns null if one or more expressions are null for Oracle enterprise 9.2.0.7 and 10.2.0.3. The docs for greatest() don't talk of NULL: http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions060. htm#SQLRF00645 There are metalink documents that do seem to make it clear greatest/least are defined to return null if one or more expressions has a null. (see doc 207279.999 for example) SQL select greatest(1,2,null,3) from dual; GREATEST(1,2,NULL,3) SQL select greatest(1,2,3) from dual; GREATEST(1,2,3) --- 3 SQL select version from v$instance; VERSION --- 9.2.0.7.0 SQL select greatest(1,2,null,3) from dual; GREATEST(1,2,NULL,3) SQL select greatest(1,2,3) from dual; GREATEST(1,2,3) --- 3 SQL select version from v$instance; VERSION --- 10.2.0.3.0 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Looking for help regarding getting the latest inserted sequence value.
--- Magnus Hagander [EMAIL PROTECTED] wrote:
If you're on 8.2 the easiest way is to use INSERT RETURNING. For example:
INSERT INTO t (somefield) VALUES ('somevalue') RETURNING pkey
with pkey being the SERIAL field.
I don't want to derail the thread too much, but would it be nice if the
returning could be used in
a insert sub-query?
INSERT INTO Childtable ( parentfkey, childname )
VALUES ( ( INSERT INTO Parenttable ( parentname )
VALUES ( 'FRED' ) RETURNING pkey ), 'Jed');
This with one statement, you could insert into two or more tables.
Regards,
Richard Broersma Jr.
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
Re: [GENERAL] greatest/least semantics different between oracle and postgres
You are correct. PostgreSQL implementation isn't compatible with Oracle. It's question if the behave can be changed now. Pavel 2007/6/30, paul rivers [EMAIL PROTECTED]: -Original Message- From: [EMAIL PROTECTED] [mailto:pgsql-general- [EMAIL PROTECTED] On Behalf Of Pavel Stehule Sent: Saturday, June 30, 2007 10:37 AM To: Bruno Wolff III; Pavel Stehule; pgsql-general@postgresql.org Subject: Re: [GENERAL] greatest/least semantics different between oracle and postgres Maybe that reference was for an earlier version of Oracle and the definition changed at some point? I only have access to version 9 and greatest and lest are strict there. I am installing OracleXE and I'll test it. Pavel At risk of putting my foot in my mouth again, greatest() returns null if one or more expressions are null for Oracle enterprise 9.2.0.7 and 10.2.0.3. The docs for greatest() don't talk of NULL: http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions060. htm#SQLRF00645 There are metalink documents that do seem to make it clear greatest/least are defined to return null if one or more expressions has a null. (see doc 207279.999 for example) SQL select greatest(1,2,null,3) from dual; GREATEST(1,2,NULL,3) SQL select greatest(1,2,3) from dual; GREATEST(1,2,3) --- 3 SQL select version from v$instance; VERSION --- 9.2.0.7.0 SQL select greatest(1,2,null,3) from dual; GREATEST(1,2,NULL,3) SQL select greatest(1,2,3) from dual; GREATEST(1,2,3) --- 3 SQL select version from v$instance; VERSION --- 10.2.0.3.0 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] Trapping errors from pl/perl (trigger) functions
Hi, I have a pl/perl trigger function which can give an error, and I would like to catch it in a pl/pgsql function, but I can't seem to trap it. Is it possible to catch errors generated pl/perl functions in a BEGIN ... EXCEPTION WHEN ... END block? Or perhaps in some other way? ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] Do Postgres exceptions rise up the stack?
A basic question about raising exceptions in Postgres: If Function A calls Function B and Func B raises an exception, will the exception roll back the transaction in Func A by default? Or do I need to trap and re-raise the exception in Func A? Thanks. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Do Postgres exceptions rise up the stack?
On Saturday 30 June 2007 23:14, Postgres User wrote: A basic question about raising exceptions in Postgres: If Function A calls Function B and Func B raises an exception, will the exception roll back the transaction in Func A by default? Or do I need to trap and re-raise the exception in Func A? Thanks. Any exception aborts the transaction. That's how exceptions work. If you don't catch them, they bubble all the way to the surface. Otherwise it would be too much like if-statement error checking. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] SQL problem..
Your query won't work because there is no single Price associated with a range of dates. It doesn't make sense. Do you mean to select AVG(Price)? On 6/28/07, Bauhaus [EMAIL PROTECTED] wrote: Hello, I'm an Access/SQL novice and I have an sql problem: I have the following table Price: FuelID PriceDate Price LPG1/05/2007 0,2 LPG13/05/2007 0,21 SPS 2/05/2007 1,1 SPS 15/05/2007 1,08 And I have to make the following query: FuelID PriceDate_from PriceDate_To Price LPG1/05/2007 13/05/2007 0,2 SPS 2/05/2007 15/05/20071,1 LPG13/05/2007 0,21 SPS 15/05/2007 1,08 I tried this: SELECT FuelID, min(FuelDate) AS Pricedate_from, max(FuelDate) AS PriceDate_to FROM Price GROUP BY FuelID; Problem is, when I put Price in the select, I get the error 'Price not part of an aggregate function' :s Eitherway, it doesnt work, I only have one FuelDate_from and one FuelDate_to if I use min max. While there should be several from...to... dates for a particular fuel. How can I solve this ? ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Do Postgres exceptions rise up the stack?
How about this scenario: func A() begin x = func B(); y = func C(); z = func D(); end Where func A, B, C, and D all update the db. If a funciton is raised in func D(), will all the transactions in the other children be rolled back? Or do I need to add code to enable this? On 6/30/07, Wiebe Cazemier [EMAIL PROTECTED] wrote: On Saturday 30 June 2007 23:14, Postgres User wrote: A basic question about raising exceptions in Postgres: If Function A calls Function B and Func B raises an exception, will the exception roll back the transaction in Func A by default? Or do I need to trap and re-raise the exception in Func A? Thanks. Any exception aborts the transaction. That's how exceptions work. If you don't catch them, they bubble all the way to the surface. Otherwise it would be too much like if-statement error checking. ---(end of broadcast)--- TIP 6: explain analyze is your friend ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Do Postgres exceptions rise up the stack?
Where func A, B, C, and D all update the db. If an EXCEPTION is raised in func D(), will all the transactions in the other children be rolled back? Or do I need to add code to enable this? On 6/30/07, Postgres User [EMAIL PROTECTED] wrote: How about this scenario: func A() begin x = func B(); y = func C(); z = func D(); end Where func A, B, C, and D all update the db. If a funciton is raised in func D(), will all the transactions in the other children be rolled back? Or do I need to add code to enable this? On 6/30/07, Wiebe Cazemier [EMAIL PROTECTED] wrote: On Saturday 30 June 2007 23:14, Postgres User wrote: A basic question about raising exceptions in Postgres: If Function A calls Function B and Func B raises an exception, will the exception roll back the transaction in Func A by default? Or do I need to trap and re-raise the exception in Func A? Thanks. Any exception aborts the transaction. That's how exceptions work. If you don't catch them, they bubble all the way to the surface. Otherwise it would be too much like if-statement error checking. ---(end of broadcast)--- TIP 6: explain analyze is your friend ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Do Postgres exceptions rise up the stack?
On Saturday 30 June 2007 23:52, Postgres User wrote: How about this scenario: func A() begin x = func B(); y = func C(); z = func D(); end Where func A, B, C, and D all update the db. If a funciton is raised in func D(), will all the transactions in the other children be rolled back? Or do I need to add code to enable this? (Noted correction in other reply). It will be rolled back. When you don't begin a transaction explicitly, postgresql creates one implicitely when you execute a function. So, calling a function will always be atomic. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] Bitmap index?
In postgresql 8.2 I need a bitmap index for a certain column, but how do I create it? (No, a btree is not sufficient. :-) ) ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Bitmap index?
On Jun 30, 2007, at 18:23 , cluster wrote: In postgresql 8.2 I need a bitmap index for a certain column, but how do I create it? (No, a btree is not sufficient. :-) ) Where do you see that PostgreSQL has bitmap indexes? http://www.postgresql.org/docs/8.2/interactive/indexes-types.html PostgreSQL can combine multiple indexes and use a bitmap scan during query execution, but this is not an on-disk bitmap index. http://www.postgresql.org/docs/8.2/interactive/indexes-bitmap-scans.html Perhaps if you give more information concerning your situation and why you believe B-tree indexes are not sufficient, others on the list might be able to provide suggestions. Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
