WG: [SQL] sql-porting-problem oracle to postgresql with UPDATE/IS NOT NULL
Sorry, it's a lapse by copying and simplification the original version. that is correct: UPDATE table1 t1 SET (t1.id) = (SELECT t3.id FROM table2 t2,table3 t3, table1 t1 WHERE t3.field = t2.field AND t2.id = t1.id AND t1.id <> t3.id) WHERE (SELECT t3.id FROM table2 t2,table3 t3, table1 t1 WHERE t3.field = t2.field AND t2.id = t1.id AND t1.id <> t3.id) IS NOT NULL; or in very simplified form: UPDATE table t1 SET (t1.id)=(SELECT expression) WHERE (SELECT expression) IS NOT NULL; The SELECT expressions are identical. this syntax is allowed on postgresql? the solution brought by Daryl Richter has no effect. Regards, Hans -Ursprüngliche Nachricht- Von: nha [mailto:[email protected]] Gesendet: Dienstag, 28. Juli 2009 23:11 An: Gau, Hans-Jürgen Cc: PgSQL-sql Betreff: Re: [SQL] sql-porting-problem oracle to postgresql with UPDATE/IS NOT NULL Hello, Le 28/07/09 14:25, Daryl Richter a écrit : > > On Jul 28, 2009, at 5:58 AM, Gau, Hans-Jürgen wrote: > >> hello list, >> i have some problems with an sql-statement which runs on oracle but >> not on postgresql (i want update only if result of SELECT is not >> empty, the SELECT-queries are identical): >> >> UPDATE table1 t1 >> SET (t1.id) = >> (SELECT h.id FROM table2 t2,table3 t3, table1 t1 >> WHERE t3.field = t2.field >> AND t2.id = t1.id >> AND t1.id <> t3.id) >> WHERE >> (SELECT h.id FROM table2 t2,table3 t3, table1 t1 >> WHERE t3.field = t2.field >> AND t2.id = t1.id >> AND t1.id <> t3.id) IS NOT NULL; >> > Try this: > > UPDATE table1 t1 [...] > WHERE > EXISTS (SELECT 1 FROM table2 t2,table3 t3, table1 t1 > WHERE t3.field = t2.field > AND t2.id = t1.id > AND t1.id <> t3.id > > AND h.id IS NOT NULL); > Beyond the solution brought by Daryl Richter, it seems that "h" is an unbound alias in the original (and also in the suggested) query. Some clarification would be helpful for further investigation. Regards. -- nha / Lyon / France. -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: WG: [SQL] sql-porting-problem oracle to postgresql with UPDATE/IS NOT NULL
Hello, Le 29/07/09 11:48, Gau, Hans-Jürgen a écrit : > Sorry, it's a lapse by copying and simplification the original version. that > is correct: > > UPDATE table1 t1 > SET (t1.id) = > (SELECT t3.id FROM table2 t2,table3 t3, table1 t1 > WHERE t3.field = t2.field > AND t2.id = t1.id > AND t1.id <> t3.id) > WHERE > (SELECT t3.id FROM table2 t2,table3 t3, table1 t1 > WHERE t3.field = t2.field > AND t2.id = t1.id > AND t1.id <> t3.id) IS NOT NULL; > > or in very simplified form: > > UPDATE table t1 > SET (t1.id)=(SELECT expression) > WHERE (SELECT expression) IS NOT NULL; > > The SELECT expressions are identical. > > this syntax is allowed on postgresql? > > the solution brought by Daryl Richter has no effect. > > Regards, Hans > [...] There is one mistake syntactically speaking and according to PostgreSQL specification for UPDATE statement: column specified in the SET part cannot be qualified with an existing alias for the updated table. See also: http://www.postgresql.org/docs/8.4/interactive/sql-update.html Except this point, the query is valid although I am not sure the result is always what you may expect because there is no relation between any field of the updated table and the SELECT expression in the WHERE clause; table1 is called (in fact, a copy of this table is implied) but no column of this table is bound to one or more of the current updated table1. I may mistake... Regards. -- nha / Lyon / France. -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: WG: [SQL] sql-porting-problem oracle to postgresql with UPDATE/IS NOT NULL
Hello again, Le 29/07/09 12:21, nha a écrit : > Hello, > > Le 29/07/09 11:48, Gau, Hans-Jürgen a écrit : >> Sorry, it's a lapse by copying and simplification the original version. that >> is correct: >> >> UPDATE table1 t1 >> SET (t1.id) = >> (SELECT t3.id FROM table2 t2,table3 t3, table1 t1 >> WHERE t3.field = t2.field >> AND t2.id = t1.id >> AND t1.id <> t3.id) >> WHERE >> (SELECT t3.id FROM table2 t2,table3 t3, table1 t1 >> WHERE t3.field = t2.field >> AND t2.id = t1.id >> AND t1.id <> t3.id) IS NOT NULL; >> [...] >> the solution brought by Daryl Richter has no effect. > > There is one mistake [...] To be concrete, a valid version would be: UPDATE table1 t1 SET id = (something) WHERE (anotherthing); > Except this point, the query is valid although I am not sure the result > is always what you may expect [...] To "join" table1 column (assumed: id) to the subquery (sub-select here) expression, table1 recall is not relevant in the subquery. Moreover an alias "t1" is already defined to table1 (at the beginning of the statement). A more suitable version would be: UPDATE table1 t1 SET id = (SELECT t3.id FROM table2 t2, table3 t3 WHERE t3.field = t2.field AND t2.id = t1.id AND t1.id <> t3.id) WHERE (SELECT t3.id FROM table2 t2, table3 t3 WHERE t3.field = t2.field AND t2.id = t1.id AND t1.id <> t3.id) IS NOT NULL; A PostgreSQL-compliant version could also be: UPDATE table1 t1 SET id = t3.id FROM table2 t2, table3 t3 WHERE t3.field = t2.field AND t2.id = t1.id AND t1.id <> t3.id AND t3.id IS NOT NULL; A PostgreSQL-compliant and faster version may be: UPDATE table1 t1 SET id = t3.id FROM table2 t2 INNER JOIN (SELECT t.id, t.field FROM table3 t WHERE t.id IS NOT NULL) t3 ON t3.field = t2.field WHERE t2.id = t1.id AND t3.id <> t1.id; Hoping a satisfying solution is up. -- nha / Lyon / France. -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Tweak sql result set... ?
On 2009-07-28, Axe wrote: > I have a problem where I want to tweak a simple select in an > "unobtrusive way". Imagine I have the following select statement: > "SELECT name FROM customer LIMIT 1" and I get a normal result set from > this. But, could I,maybe by defining some other function or similar, > change the result set *without* changing the query? Suppose I get the > result from the query above, saying: "Peter Peterson". I would > (sometimes) like to get the result "Peter Peterson" but I > should not have to change the original query. > > I know I could write "SELECT '' || name || '' as name FROM > customer" but then I have altered the original query and I cannot do > this since it is supposed to function different in two different > situations. > > Any ideas on how to achieve this? I would like to let the original sql > code stay original. I can prepare postgres before executing the sql if > this makes it easier to acheive the goal put a wrapper round whatever it is you use to send the queries that modifies the returned values. -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] sql-porting-problem oracle to postgresql with UPDATE/IS NOT NULL
On Jul 28, 2009, at 5:10 PM, nha wrote: Hello, Le 28/07/09 14:25, Daryl Richter a écrit : On Jul 28, 2009, at 5:58 AM, Gau, Hans-Jürgen wrote: hello list, i have some problems with an sql-statement which runs on oracle but not on postgresql (i want update only if result of SELECT is not empty, the SELECT-queries are identical): UPDATE table1 t1 SET (t1.id) = (SELECT h.id FROM table2 t2,table3 t3, table1 t1 WHERE t3.field = t2.field AND t2.id = t1.id AND t1.id <> t3.id) WHERE (SELECT h.id FROM table2 t2,table3 t3, table1 t1 WHERE t3.field = t2.field AND t2.id = t1.id AND t1.id <> t3.id) IS NOT NULL; Try this: UPDATE table1 t1 [...] WHERE EXISTS (SELECT 1 FROM table2 t2,table3 t3, table1 t1 WHERE t3.field = t2.field AND t2.id = t1.id AND t1.id <> t3.id AND h.id IS NOT NULL); Beyond the solution brought by Daryl Richter, it seems that "h" is an unbound alias in the original (and also in the suggested) query. Some clarification would be helpful for further investigation. Ahh, you're right. I didn't even notice that, just reformatted the OPs query. Regards. -- nha / Lyon / France. -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql -- Daryl -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
WG: WG: [SQL] sql-porting-problem oracle to postgresql with UPDAT E/IS NOT NULL
it runs without alias t1. before fieldname id after SET, so: UPDATE table1 t1 SET id = (SELECT t3.id FROM table2 t2,table3 t3, table1 t1 WHERE t3.field = t2.field AND t2.id = t1.id AND t1.id <> t3.id) WHERE (SELECT t3.id FROM table2 t2,table3 t3, table1 t1 WHERE t3.field = t2.field AND t2.id = t1.id AND t1.id <> t3.id) IS NOT NULL; Of course, simply... Thanks for the help! -Ursprüngliche Nachricht- Von: nha [mailto:[email protected]] Gesendet: Mittwoch, 29. Juli 2009 13:04 An: Gau, Hans-Jürgen Cc: PgSQL-sql Betreff: Re: WG: [SQL] sql-porting-problem oracle to postgresql with UPDATE/IS NOT NULL Hello again, Le 29/07/09 12:21, nha a écrit : > Hello, > > Le 29/07/09 11:48, Gau, Hans-Jürgen a écrit : >> Sorry, it's a lapse by copying and simplification the original version. that >> is correct: >> >> UPDATE table1 t1 >> SET (t1.id) = >> (SELECT t3.id FROM table2 t2,table3 t3, table1 t1 >> WHERE t3.field = t2.field >> AND t2.id = t1.id >> AND t1.id <> t3.id) >> WHERE >> (SELECT t3.id FROM table2 t2,table3 t3, table1 t1 >> WHERE t3.field = t2.field >> AND t2.id = t1.id >> AND t1.id <> t3.id) IS NOT NULL; >> [...] >> the solution brought by Daryl Richter has no effect. > > There is one mistake [...] To be concrete, a valid version would be: UPDATE table1 t1 SET id = (something) WHERE (anotherthing); > Except this point, the query is valid although I am not sure the result > is always what you may expect [...] To "join" table1 column (assumed: id) to the subquery (sub-select here) expression, table1 recall is not relevant in the subquery. Moreover an alias "t1" is already defined to table1 (at the beginning of the statement). A more suitable version would be: UPDATE table1 t1 SET id = (SELECT t3.id FROM table2 t2, table3 t3 WHERE t3.field = t2.field AND t2.id = t1.id AND t1.id <> t3.id) WHERE (SELECT t3.id FROM table2 t2, table3 t3 WHERE t3.field = t2.field AND t2.id = t1.id AND t1.id <> t3.id) IS NOT NULL; A PostgreSQL-compliant version could also be: UPDATE table1 t1 SET id = t3.id FROM table2 t2, table3 t3 WHERE t3.field = t2.field AND t2.id = t1.id AND t1.id <> t3.id AND t3.id IS NOT NULL; A PostgreSQL-compliant and faster version may be: UPDATE table1 t1 SET id = t3.id FROM table2 t2 INNER JOIN (SELECT t.id, t.field FROM table3 t WHERE t.id IS NOT NULL) t3 ON t3.field = t2.field WHERE t2.id = t1.id AND t3.id <> t1.id; Hoping a satisfying solution is up. -- nha / Lyon / France. -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Tweak sql result set... ?
> > Any ideas on how to achieve this? I would like to let the original sql > > code stay original. I can prepare postgres before executing the sql if > > this makes it easier to acheive the goal > > Have a look at CREATE RULE. > > Tim > Ok, you mean I could create a rule for the table, then I let the script go into my "black box", do the original selects, but get a manipulated result set back, then I drop the rule so that the blackbox of scripts can get the not manipulated result set back? I need to sometimes get the result "output from query" and sometimes not and it would be awesomw to get it with the same query, but maybe by setting a rule or similar. Best from performance view would be to make something more permanent. Is it possible to activate/deactivate a rule? Axe -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Foreign keys and fixed values...
Hello, I'm having a problem using foreign keys, or at least in a way i find good. Basicly I have a table I wish to use as a foreign key with lookup "id" and "code", like: 1,a 1,b 1,c 2,a 2,d I have of course an UNIQUE on (id,code). However, in the table I want the key to apply to, I only have one column that I want to restrict to one lookup. Basicly I want to do something like: ALTER TABLE data ADD FOREIGN KEY ( 2, col ) REFERENCES lookups ( id, code ) In this case 2 is a literal integer, not a column reference and the result should be that a and d is legal - but this command isn't legal. Options: 1) Add another column to my data table like: ALTER TABLE data ADD COLUMN dummy INTEGER DEFAULT 2 ALTER TABLE data ADD FOREIGN KEY ( dummy, col ) REFERENCES lookups ( id, code ) This works, but creates a useless column in my data table that feels very unclean. 2) Refactor the lookup table to have one lookup per table. However, this also means I'll probably need to duplicate translation tables, value mapping tables etc. and a bunch of queries would have to dynamically alter table names. It seems more than a little messy. 3) Forego integrity checking in the database and do it in the app. However, I sense trouble with that apporach in the future as I hope several app boxes can do work on the same database. 4) Trigger? Something else? The way I'd like to do it seems like the cleanest, if only it'd work. Why can't the foreign key evaluate the literal, in the same way it could in a query? It's possible that I'm way outside all SQL standards now but it seems like sensible functionality to me. Regards, Kjell Rune _ Alt i ett. Få Yahoo! Mail med adressekartotek, kalender og notisblokk. http://no.mail.yahoo.com -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] SQL report
I have the following senario. I have a tracking system. The system will record the status of an object regularly, all the status records are stored in one table. And it will keep a history of maximum 1000 status record for each object it tracks. The maximum objects the system will track is 100,000. Which means I will potentially have a table size of 100 million records. I have to generate a report on the latest status of all objects being tracked at a particular point in time, and also I have to allow user to sort and filter on different columes in the status record displayed in the report. The following is a brief description in the status record (they are not actual code) ObjectRecord( objectId bigint PrimaryKey desc varchar ) StatusRecord ( id bigint PrimaryKey objectId bigint indexed datetime bigint indexed capacity double reliability double efficiency double ) I have tried to do the following, it works very well with around 20,000 objects. (The query return in less than 10s) But when I have 100,000 objects it becomes very very slow. (I don't even have patience to wait for it to return I kill it after 30 mins) select * from statusrecord s1 INNER JOIN ( SELECT objectId , MAX(datetime) AS msdt FROM statusrecord WHERE startDatetime <= 1233897527657 GROUP BY objectId ) AS s2 ON ( s1.objectId = s2.objectId AND s1.datetime = s2.msdt ) where ( capacity < 10.0 ) order by s1.datetime DESC, s1.objectId DESC; I did try to write a store procedure like below, for 100,000 objects and 1000 status records / object, it returns in around 30 mins. CREATE OR REPLACE FUNCTION getStatus(pitvalue BIGINT) RETURNS SETOF statusrecord AS $BODY$ DECLARE id VARCHAR; status statusrecord%ROWTYPE; BEGIN FOR object IN SELECT * FROM objectRecord LOOP EXECUTE 'SELECT * FROM statusrecord WHERE objectId = ' || quote_literal(object.objectId) || ' AND datetime <= ' || quote_literal(pitvalue) || ' ORDER BY datetime DESC' INTO status; IF FOUND THEN RETURN NEXT status; END IF; END LOOP; RETURN; END $BODY$ LANGUAGE plpgsql; Just wanna to know if anyone have a different approach to my senario. Thanks alot. John
