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 (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql -- Daryl -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) 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: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 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 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); thanks, hans -- Daryl http://itsallsemantics.com Everyone thinks of changing the world, but no one thinks of changing himself. - Leo Tolstoy -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] i have table
On 10/4/06 12:20 PM, Aaron Bono [EMAIL PROTECTED] wrote: On 10/4/06, Erik Jones [EMAIL PROTECTED] wrote: Aaron Bono wrote: On 10/4/06, *Erik Jones* [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] wrote: There is one non-SQL related reason that I like to be able to order columns, at least the way they are displayed whenever the table is described: human comprehension. For example, I like to group all keys in a table before data, that includes primary as well as foreign keys. So, say I'm building on to an existing application and I need to do an ALTER TABLE on an existing table to add a foreign key to an existing table. I'd like that key to be listed with the other keys, but presently that's not possible in a simple way and, to be honest, I usually just go without as the process you've described below is too prone to user (human) error when dealing with live, sensitive data for me to want to mess with it. Ah, but it is possible... if you use views. I recommend you build views and query off them. Then you can control the order the columns will appear. Which would be great if I didn't have (many) thousands of lines of code that already use the tables. Besides, this is no where near a 'make or break' thing. It's just a matter of aesthetic preference. Ah, but it *is* a make or break thing. I have seen more than one application crash because some developer didn't understand that columns in a relation (table) have no defined order. This (along with its sister axiom that rows have no defined order) is one of the most commonly misunderstood aspects of relational databases. So do it as needed and convert your application slowly. I just name my views as table_name_vw so all you have to do is modify your queries to hit the _vw instead of just the table. That shouldn't take much time to refactor. == Aaron Bono Aranya Software Technologies, Inc. http://www.aranya.com http://codeelixir.com == -- Daryl http://itsallsemantics.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] i have table
On 10/3/06 6:47 AM, Penchalaiah P. [EMAIL PROTECTED] wrote: Hi ... I have one table with 12 fields.. now I want to add one more field in this table.. but that field has to come next to cda_no.. I mean as a 3rd field.. If I am adding that field it is coming last field ... The columns in a relation (table) are not ordered, so this question makes no sense. Why do you feel it necessary to have that field next to the other? may I know how it is possible to that table... ThanksRegards Penchal reddy | Software Engineer -- Daryl http://itsallsemantics.com I¹m afraid of the easy stuff its always harder than it seems -- Bill Hampton, 2006 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] nested select within a DISCTINCT block
On 9/14/06 1:13 PM, zqzuk [EMAIL PROTECTED] wrote: Hi, here i have a problem with this task... I have a table cancellation which stores cancelled bookings and details of charges etc and a table bookings which stores details of bookings, for example: cancellation(cancellation_id, booking_id, charge) booking(booking_id, customer_id, product_package_id, details) in the query, i wish to find, how many customers have booked for each product_package_id. if there were 3 bookings for product_package_id=1, and all these are cancelled and therefore exist in cancellation, then the query result shoud display something like package_id, #of bookings 1 0 here are what i tried select distinct b.product_package_id, count (distinct b.customer_id and not exists (select cc from cancellation cc where cc.booking_id=b.booking_id)) from booking as b group by b.product_package_id and it doesnt work. the syntax within the DISTINCT is wrong, unsurprisingly. i also tried select distinct b.product_package_id, count (distinct b.customer_id not in (select cc from cancellation cc where cc.booking_id=b.booking_id)) from booking as b group by b.product_package_id it produced incorrect result. ie, for those canceled bookings are also counted, producing package_id, #of bookings 1 3 which supposed to be package_id, #of bookings 1 0 could anyone give any hints please, many thanks ! create table booking(booking_id int, customer_id int, product_package_id int, details text); create table cancellation(cancellation_id int , booking_id int, charge decimal); insert into booking values( 1, 1, 1, 'Cxl Booking 1' ); insert into booking values( 2, 2, 1, 'Cxl Booking 2' ); insert into booking values( 3, 2, 1, 'Ok Booking 3' ); insert into booking values( 4, 3, 2, 'Cxl Booking 4' ); insert into cancellation values( 1, 1, 1.00 ); insert into cancellation values( 2, 2, 1.00 ); insert into cancellation values( 3, 4, 1.00 ); select distinct product_package_id, ( select count(booking_id) from booking b2 where b2.product_package_id = b1.product_package_id and not exists ( select 1 from cancellation c where c.booking_id = b2.booking_id ) ) as uncancelled_bookings from booking b1 order by product_package_id; product_package_id uncancelled_bookings - --- 1 1 2 0 2 record(s) selected [Fetch MetaData: 2/ms] [Fetch Data: 0/ms] [Executed: 9/14/06 5:56:07 PM EDT ] [Execution: 86/ms] -- Daryl http://itsallsemantics.com I¹m afraid of the easy stuff its always harder than it seems -- Bill Hampton, 2006 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Joint a table back on itself?
On 9/12/06 11:55 AM, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Hi all, I have a union query that generates a table with directional measurments (a=azimuth, i=depth) at various depths (md) down a hole. The results look like: hole_id | md | a| i|e |n |v ---++++--+--+- - GND-06-65 | 0 | 90 |-75 | 795187.927 | 9228405.685 | 3945.199 GND-06-65 | 19 | 90.37 | -74.42 | 795192.937315893 | 9228405.66852282 | 3926.87160812059 GND-06-65 | 28 | 91.18 | -74.49 | 795195.348994385 | 9228405.63593718 | 3918.20081588081 GND-06-65 | 37 | 91.04 | -74.53 | 795197.752173187 | 9228405.5893705 | 3909.52772202531 GND-06-65 | 46 | 92.38 | -74.56 | 795200.149282893 | 9228405.51783377 | 3900.85313364721 GND-06-65 | 55 | 92.86 | -74.55 | 795202.543576384 | 9228405.40826886 | 3892.17815120329 The depths ( md column) will always start with zero and the intervals will be variable. So how can I join this view back onto itself so each record is joined to the next record? Such as: md1 | a1 | i1 |e1| n1|v1| md2 | a2 | i2 |e2|n2| v2| +++--+--+- -|+++--+--+--- --- 0 | 90 |-75 | 795187.927 | 9228405.685 | 3945.199 | 19 | 90.37 | -74.42 | 795192.937315893 | 9228405.66852282 | 3926.87160812059 19 | 90.37 | -74.42 | 795192.937315893 | 9228405.66852282 | 3926.87160812059 | 28 | 91.18 | -74.49 | 795195.348994385 | 9228405.63593718 | 3918.20081588081 28 | 91.18 | -74.49 | 795195.348994385 | 9228405.63593718 | 3918.20081588081 | 37 | 91.04 | -74.53 | 795197.752173187 | 9228405.5893705 | 3909.52772202531 37 | 91.04 | -74.53 | 795197.752173187 | 9228405.5893705 | 3909.52772202531 | 46 | 92.38 | -74.56 | 795200.149282893 | 9228405.51783377 | 3900.85313364721 46 | 92.38 | -74.56 | 795200.149282893 | 9228405.51783377 | 3900.85313364721 | 55 | 92.86 | -74.55 | 795202.543576384 | 9228405.40826886 | 3892.17815120329 My reason for wanting this is so I can joint this table with a between clause to another table with depth measurments recorded along this hole and perform a calculation. Leaving out the extra columns: create table holes( hole_id text, md int, a decimal ); insert into holes( hole_id, md, a ) values ( 'GND-06-65', 0, 90 ); insert into holes( hole_id, md, a ) values ( 'GND-06-65', 19, 90.37 ); insert into holes( hole_id, md, a ) values ( 'GND-06-65', 28, 91.18 ); insert into holes( hole_id, md, a ) values ( 'GND-06-65', 37, 91.04 ); insert into holes( hole_id, md, a ) values ( 'GND-06-65', 46, 92.38 ); insert into holes( hole_id, md, a ) values ( 'GND-06-65', 55, 92.86 ); go select a.md as md1, a.a as a1, b.md as md2, b.a as a2 from holes a join holes b on b.hole_id = a.hole_id where b.md = ( select min( c.md ) from holes c where c.hole_id = a.hole_id and c.a a.a ) order by a.md, b.md; md1 a1 md2 a2 -- - -- - 0 90 19 90.37 19 90.37 28 91.18 28 91.18 46 92.38 37 91.04 28 91.18 46 92.38 55 92.86 5 record(s) selected [Fetch MetaData: 8/ms] [Fetch Data: 0/ms] [Executed: 9/12/06 2:05:52 PM EDT ] [Execution: 123/ms] Thanks, Phillip J. Allen Consulting Geochemist [EMAIL PROTECTED] The union query is as follows: SELECT c.hole_id, 0 AS md, c.collar_azimuth AS a, c.collar_dip AS i, c.e_utm AS e, c.n_utm AS n, c.elv_utm AS v FROM dh_collar AS c WHERE (((c.hole_id)='GND-06-65')) UNION SELECT s.hole_id, s.depth_m AS md, s.azimuth AS a, s.vert_dip AS i, s.XE AS e, s.XN AS n, s.XR AS v FROM dh_survey AS s WHERE (((s.hole_id)='GND-06-65')) UNION SELECT s.hole_id, c.depth_m AS md, s.azimuth AS a, s.vert_dip AS i, s.XE AS e, s.XN AS n, s.XR AS v FROM dh_collar AS c RIGHT JOIN dh_survey AS s ON c.hole_id = s.hole_id WHERE (((s.depth_m)= ( SELECT Max(stmp.depth_m) AS MaxOfdepth_m FROM dh_survey AS stmp GROUP BY stmp.hole_id HAVING (((stmp.hole_id)='GND-06-65')) )) AND ((s.hole_id)='GND-06-65')); ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq -- Daryl Email *my = [ daryl at: @eddl dot: @us ]; Weblog *blog = @²http://itsallsemantics.com²; ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Substitute a Character
On 9/6/06 12:53 PM, Judith [EMAIL PROTECTED] wrote: Hello everybody!! I have a field type text with folios like this: A98526 but I want to change de A for a 0 like this: 098526, exists a way to do this in a query??? select translate( 'A98526', 'A', '0' ); translate 098526 1 record(s) selected [Fetch MetaData: 1/ms] [Fetch Data: 0/ms] [Executed: 9/6/06 4:18:44 PM EDT ] [Execution: 99/ms] Thanks in advanced!!! ---(end of broadcast)--- TIP 6: explain analyze is your friend -- Daryl Email *my = [ daryl at: @eddl dot: @us ]; Weblog *blog = @²http://itsallsemantics.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: [SQL] how do I check for lower case
On 8/10/06 4:32 PM, Juliann Meyer [EMAIL PROTECTED] wrote: I have a table with a column, lets call it identifier, that is defined as varchar(8) that should never contain lower case letters. Its a large table. Is there a way to query the table to see if any values in this column are lower case and to get a list out? The user interface application that users use prevents them from adding an entry in lower case now, but didn't in earlier version. select * from table where column ~ '[a-z]' Julie ---(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 -- Daryl Hell, there are no rules here-- we're trying to accomplish something. -- Thomas A. Edison ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Advanced Query
On Jun 6, 2006, at 12:32 PM, Andrew Sullivan wrote: On Tue, Jun 06, 2006 at 09:20:13AM -0700, codeWarrior wrote: I would hope that your choice to use postgreSQL is because it is superior technology that scales well financially... not because you get a warm fuzzy from all your friends on the mailing lists... I would hope that the tone of the mailing lists might be maintained in much the high one demonstrated by such polite, helpful, and smarter-than-me people as those on the PostgreSQL core team. I note that the above troll does not qualify. I suggest people avoid feeding it. update thread set response = response + 1; A -- Andrew Sullivan | [EMAIL PROTECTED] Information security isn't a technological problem. It's an economics problem. --Bruce Schneier ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster -- Daryl self email: ( daryl at: eddl dot: us ) ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] bug with if ... then ... clause in views
On Jan 18, 2006, at 4:18 AM, Emil Rachovsky wrote: While trying to create some views I stumbled on some problem with using the if-then clause. Here is a simple example : CREATE OR REPLACE VIEW public.SomeView as select d.id, if (true) then d.DocNumber endif from public.Z_Documents as d; I get the following error : syntax error at or near then at character 72 I don't have a clue what is going on here. Any suggestions? I see 2 issues. 1) if *what* is true? 2) AFAIK, there is no IF conditional in SQL. Perhaps you want CASE? e.g. CREATE OR REPLACE VIEW SomeView as select d.id, case when condition = true then d.doc_number else 'Bad Doc' end from documents as d; go Thanks in advance, Emil -- Daryl Stress rarely has a positive impact on our ability to think. Never, I'd guess. -- Ron Jeffries, 2005 ---(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: [SQL] constraint and ordered value
On Dec 29, 2005, at 2:16 AM, Bruno Wolff III wrote: On Wed, Dec 28, 2005 at 00:52:18 +0700, David Garamond [EMAIL PROTECTED] wrote: Is it possible to use only CHECK constraint (and not triggers) to completely enforce ordered value of a column (colx) in a table? By that I mean: 1. Rows must be inserted in the order of colx=1, then colx=2, 3, and so on; 2. When deleting (or updating), holes must not be formed, e.g. if there are three rows then row with colx=3 must be the first one deleted, and then colx=2 the second, and so on. I can see #1 being accomplished using a NOT NULL + UNIQUE constraint and a CHECK constraint that calls some PL function where the function does a simple checking (new.colx=1 if COUNT(colx)==0, or new.colx=MAX (colx)+1). But is it possible to do #2 using only constraints? No. A constraint only applies to one row at a time. If you try to work around this by calling a function that does queries it isn't guarenteed to work. And if you are thinking of calling a function that does a query, you aren't looking at saving time over using triggers. Also, if you are going to have concurrent updates, you are going to need to do table locking to make this work. And, finally, you should ask yourself *why* are you doing this, given that one of the fundamental properties of a table (relation) is that the rows (tuples) are *unordered.* So much of what makes a relational db a wonderful thing for storing data depends on this notion. If you provide an explanation of what you are trying to model, perhaps we can help you find a better schema design. [snip] -- Daryl (setq email '( daryl at eddl dot us )) ---(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: Fwd: Re: [SQL] Referencing
[EMAIL PROTECTED] wrote: Quoting Daryl Richter [EMAIL PROTECTED]: [EMAIL PROTECTED] wrote: Quoting Daryl Richter [EMAIL PROTECTED]: It's hard to say without knowing more precisely what you are trying to model, but I think this push you in the right direction: Okay, but references between (output/input) and ACTIVITY tables is 1 to N. OUTPUT/INPUT - 1 to ACTIVITY - N. And not N to 1 how the example. Then the reference field need to be on ACTIVITY (send/buy) table. Ahh, ok. In that case I reverse it like so: -- This table hold everything in common for inputs/outputs create table transfer( idserial primary key ); Yes, I think it was what I wanted. And how I check if a register in Transfer table is only referenciable by ONE table (OR output OR input)?? Would I create a Trigger like: CREATE or REPLACE FUNCTION TG_output_check() RETURNS TRIGGER AS $$ BEGIN IF exists (select 1 from input where transfer_id=NEW.transfer_id) THEN Raise Exception 'This activity (transfer) is alread setted to INPUT'; END IF; RETURN NEW; END; $$ language 'plpgsql'; CREATE TRIGGER TG_output_check BEFORE INSERT or UPDATE on OUTPUT EXECUTE PROCEDURE TG_output_check(); CREATE or REP...--- and the some function to INPUT --- Or is there another way to check it? Thank you again. Exactly, except for the small change that your trigger declaration needs for each row as shown below: CREATE TRIGGER TG_output_check BEFORE INSERT or UPDATE on output for each row EXECUTE PROCEDURE TG_output_check(); -- Daryl ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: Fwd: Re: [SQL] Referencing
[EMAIL PROTECTED] wrote: Quoting Daryl Richter [EMAIL PROTECTED]: It's hard to say without knowing more precisely what you are trying to model, but I think this push you in the right direction: [snipped old schema] Okay, but references between (output/input) and ACTIVITY tables is 1 to N. OUTPUT/INPUT - 1 to ACTIVITY - N. And not N to 1 how the example. Then the reference field need to be on ACTIVITY (send/buy) table. Ahh, ok. In that case I reverse it like so: -- This table hold everything in common for inputs/outputs create table transfer( idserial primary key ); create table output( transfer_id int primary key references transfer, clientinteger, --references clientes, fiscal_number varchar(30), print_datedate ); create table input( transfer_id intprimary key references transfer, supplier integer, -- references suppliers, employee varchar(30) ); create table activity( id serial primary key, transfer_id intreferences transfer, product_id integer, --references value money ); create table financial( id serial primary key, cred_debsmallint, value money, activity_id integer references activity ); create view buy as select a.id as input_id, -- or buy_id, etc. ... b.supplier, b.employee, c.id as activity_id, c.product_id, c.value from transfer a join inputb on b.transfer_id = a.id join activity c on c.transfer_id = a.id; If this is still not what you're after and you would like additional assistance, it would really help to have at least a few rows of sample data. Good luck! -- Daryl Richter Platform Author Director of Technology (( Brandywine Asset Management ) ( Expanding the Science of Global Investing ) ( http://www.brandywine.com )) ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: Fwd: Re: [SQL] Referencing
[EMAIL PROTECTED] wrote: Ok, But the problem is becouse the buy and send tables referencing with other father table, wich is different. I shoud not create a spent table to put the buy and send values becouse the entire database is more complex than it. look: create table output( id serial primary key, client integer references clientes, fiscal_number varchar(30), print_date date, ... ); create table SEND( id serial primary key, output integer references input, product_id integer,--references value money ); create table input( id serial primary key, supplier integer references suppliers, employee varchar(30), ... ); create table BUY( id serial primary key, input integer references input, product_id integer,--references value money ); ---and--- create table financial( id serial primary key, cred_deb smallint, value money, references integer references ???, ---HERE IS THE PROBLEM, it will reference to buy OR send table ); How looked, the buy and the send table is identical except the father references (INPUT or OUTPUT)... Then I shoud not create ONE table (spent) wich has these informations. And now my question: Is there a way to references (financial) with two diferents tables in the some row? Or need I create two diferents rows??? Thanks. (sorry for my english). It's hard to say without knowing more precisely what you are trying to model, but I think this push you in the right direction: -- This table takes the place of both SEND and BUY create table activity( id serial primary key, product_id integer, --references value money ); create table financial( id serial primary key, cred_debsmallint, value money, activity_id integer references activity ); create table output( idserial primary key, clientinteger, --references clientes, fiscal_number varchar(30), print_datedate, activity_id integer references activity ); create table input( id serial primary key, supplier integer, -- references suppliers, employee varchar(30), activity_id integerreferences activity ); And then you do the following: create view buy as select a.id, b.id as input_id, a.product_id, a.value from activity a join inputb on b.activity_id = a.id; The SELL view is left as an exercise for the reader. -- Daryl Richter Platform Author Director of Technology (( Brandywine Asset Management ) ( Expanding the Science of Global Investing ) ( http://www.brandywine.com )) ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Merging lines with NULLs (with example data)
Harald Fuchs wrote: In article [EMAIL PROTECTED], MaXX [EMAIL PROTECTED] writes: How can I merge this gday,count_udp,count_tcp '2005-10-20','','2' '2005-10-20','3','' '2005-10-21','','1' '2005-10-21','5','' into that: gday,count_udp,count_tcp '2005-10-20','3','2' '2005-10-21','5','1' in a single query??? Try something like that: SELECT to_date (tstamp,'-MM-DD') AS gday, sum(CASE WHEN proto = 'UDP' THEN 1 ELSE 0 END) AS count_udp, sum(CASE WHEN proto = 'TCP' THEN 1 ELSE 0 END) AS count_tcp FROM test WHERE tstamp = now() - INTERVAL '$days DAYS' AND dst_port = $port GROUP BY gday ORDER BY gday Or, via a subquery: select distinct to_date(tstamp,'-MM-DD') as gday, ( select count(id) from test t1 where proto='UDP' and to_date(t1.tstamp,'-MM-DD') = to_date(test.tstamp,'-MM-DD') ) as count_udp, ( select count(id) from test t1 where proto='TCP' and to_date(t1.tstamp,'-MM-DD') = to_date(test.tstamp,'-MM-DD') ) as count_tcp from test where tstamp = (now() - interval '6 days' ) and dst_port = 2290 order by gday; Harald's solution is better for your particular case and will almost certainly be faster, but subqueries are good to know how to do. :) ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org -- Daryl Richter Platform Author Director of Technology (( Brandywine Asset Management ) ( Expanding the Science of Global Investing ) ( http://www.brandywine.com )) ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] automatic update or insert
tobbe wrote: Hi. I have a little problem. In a system of mine i need to insert records into table [tbStat], and if the records exist i need to update them instead and increase a column [cQuantity] for every update. I.e. the first insert sets cQuantity to 1, and for every other run cQuantity is increased. Regardless of how you implemented it, this seems unwise. You can never know, nor validate, that this quantity is definitely correct. Why can't you just insert another row and then count them? If this is a transient value you might be ok, but I generally wouldn't put it in a DB in that case anyway... Currently i have implemented this as a stored procedure in the plpgsql language. This means that in my stored procedure i first do a select to find out if the row exists or not, then i do a insert or update depending if the row existed. Unfortunately, stored procedures seems awfully slow. And i need the application to go faster. One solution could be to implement the stored procedure in my program instead. I think that this will be atleast 50% faster than my stored procedure, so that would be ok. However, this has made me thinking. Couldn't this be done directly in SQL? Brgds Robert ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq -- Daryl Richter Platform Author Director of Technology (( Brandywine Asset Management ) ( Expanding the Science of Global Investing ) ( http://www.brandywine.com )) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Design problem : using the same primary keys for inherited
David Pradier wrote: Most of the inheritance i've seen done in databases retain the parent primary as a foreign key and a primary key. That being said, only you and your team can decide if more than one object will extend a base class. If you were doing something more like this person - sweepstakes entry to model a sweepsakes entry is a person, and you allow a person to enter a sweepstakes more than once, but to enter a contest the user must provide a unique email address, then you could not just use a foreign key as the primary key in sweepstakes, since the primary key would disallow multiple entries in sweepstakes entry, you would then use a serial data type in both person and sweepstakes along with the foriegn key in sweepstakes from person. The answer depends on the need. Hope that helps. Thanks Russ, but well... It doesn't help me a lot. Our needs seem to allow that we use an id as primary key and foreign key at the same time. What i fear more is that it be against a good database design practice, because leading to potential problems. I give a clearer example : CREATE TABLE actor ( id_actor serial PRIMARY KEY, arg1 type1, arg2 type2 ) CREATE TABLE person ( id_person INTEGER PRIMARY KEY REFERENCES actor, arg3 type3, arg4 type4 ) Don't you think it is a BAD design ? If it isn't, well, it will expand my database practices. It *is* a bad design. You should not do this. After all, how is that any different than this? CREATE TABLE actor_person ( id_actor serial PRIMARY KEY, arg1 type1, arg2 type2 arg3 type3, arg4 type4 ) Furthermore, inheritance is almost certainly the wrong relationship type here. Normally, Actor would be a Role that a Person would be playing: create table role( id serial primary key,-- 1 name char(20) not null-- Actor ); create table person( id serial primary key, -- 1 namechar(20) not null,-- David role_id int not null references role -- 1 ); David -- Daryl Richter Director of Technology (( Brandywine Asset Management ) ( Expanding the Science of Global Investing ) ( http://www.brandywine.com )) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] SEVEN cross joins?!?!?
Frank Bax wrote: At 09:00 AM 10/12/05, Daryl Richter wrote: Richard Huxton wrote: Frank Bax wrote: [snip] Richard, you've summed it up nicely. Splitting locations into subsets (like 2,2,3) doesn't work because it is possible that low values in one location can be offset by high values in another location, and still result in an excellent combo. The good news is these suggestions got me thinking outside the box. I think I can program a modified brute-force that bypasses large numbers of combos early. It might still be too large/slow, so I'd be interested in finding more info about these smarter algorithms in option 2. Where do I look? If you're mathematically inclined, I would first look at using Lagrangian Relexation, it may be appropriate for your problem: http://www.2112fx.com/lagrange.html Greg: my son's the gamer; I'm just trying to help him out. ---(end of broadcast)--- TIP 6: explain analyze is your friend -- Daryl Richter Director of Technology (( Brandywine Asset Management ) ( Expanding the Science of Global Investing ) ( http://www.brandywine.com )) ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] SEVEN cross joins?!?!?
Richard Huxton wrote: Frank Bax wrote: Are you saying that you WANT to generate a cross-join, score the millions of results and then pick the best 10? It's doing what you want, but you'd like it to be faster. Or are you saying that you'd like to avoid the explosion in rows altogether? In either case - I don't suppose you could provide a real example of the query, so we can see exactly what you're trying to do. There is no best 10. I currently limit each subselect to 10 items so that query will actually run without crashing. I would like to remove the ORDER BY itemid LIMIT 10 mentioned above. At the end of the query I have a LIMIT 100 clause which will stay and produces a list of best 100 combos. Either of your solutions would be acceptable; since avoiding the explosion would also make the query faster. Current calculations indicate that running the query without LIMIT 10 in subselect would take years to process. OK - so at the heart of the problem is the fact that you want to search a space with 100 billion possible states. There are three main options 1. Brute force and patience - simple and is guaranteed to produce the best answers. You can use cursors/multiple queries to manage the workload. The disadvantage is that it's probably slower than you'd like. 2. Smarter algorithms - possibly something genetic to work towards local maxima. Or, if you'd like to keep it simple, just split your 7 locations into 2,2,3 and solve for each separately. 3. Statistical - examine a subset of possible states and accept you'll only be able to say almost best to 99% confidence or similar. I'd be tempted by #2 - there are probably some combinations you can rule out, which combined with a split/recombine should reduce the number of states to query. I'll second this recommendation. The OP is trying to drive a nail with a screwdriver. He needs a program, not a query. That any help? -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings -- Daryl Richter (daryl (at)(brandywine dot com)) ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] changing a column's position in table, how do you do that
Ferindo Middleton Jr wrote: Is there a way to change the position attribute of a column in a table? I have data that I need to import into various tables in my db on a consistent basis... I usually us e the COPY ... FROM query but I can't control the -order- of the fields my client dumps the data so I would like to be able to change the position the columns in my table to be able to better align the data to be imported with the format of my table. I was thinking I could do something like ALTER TABLE ALTER COLUMN ... or something like that to change the columns position in the table but I can't figure out how. Ferindo ---(end of broadcast)--- TIP 6: explain analyze is your friend Constantly changing the column order of a table doesn't make sense (and is a pain!). I would suggest setting up a temporary staging table with generic column names to take in the input data and then using SQL to move it into its permanent location. -- Daryl We want great men who, when fortune frowns, will not be discouraged. -- Colonel Henry Knox, 1776 ---(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: [SQL] how to do 'deep queries'?
Anthony Molinaro wrote: that query is 100% correct. it's just an equijoin (a type of inner join) between 3 tables. the syntax you show is how queries should be written and is more representative of what a joins between relations really are: Cartesian products with filters applied the ansi syntax, the explicit JOIN ... ON stuff is (imho) unnecessary, useful only for outer joins since all the vendors did it differently. Whether you feel that is unnecessary or not, it *is* the ANSI Standard and is thus, by definition, how queries should be written. In addition to cleaning up the outer join issue, it was added to make the *intention* of the query clearer. Because others are likely to read your query many more times than you write it, clarity of intent *is* important. what you have will work for postgreSQL, I used the syntax you show in my book for every single join recipe except for outjoins. are you seeing errors? regards, Anthony [original snipped] -- Daryl We want great men who, when fortune frowns, will not be discouraged. -- Colonel Henry Knox, 1776 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Updating cidr column with network operator
Michael Fuhr wrote: On Mon, Sep 26, 2005 at 12:34:59PM +0200, Axel Rau wrote: Am 26.09.2005 um 02:05 schrieb Michael Fuhr: On Fri, Sep 23, 2005 at 09:19:25PM +0200, Axel Rau wrote: I'm sure this would be the cleanest solution but remember networks change. Yes, which is why it's a good idea to automatically propogate those changes to tables that maintain redundant data. I would not call it redundant but normalized, because network has some attributes, common to all addresses in the net, 1st of all the netmask. An attribute is redundant if it repeats a fact that can be learned without it. If one table contains IP addresses and another contains networks, then you can associate IP addresses and networks with a join of the two tables; indeed, this is how the fix the network column update works. Having a network column in the address table simply repeats what could be learned through the join. I agree with Michael here. I think the fundamental problem with your schema is that it is possible to have contradictory data between the network and address table, always a bad situation. I would replace network.id with a serial type value and make the cidr a separate column, for example: CREATE TABLE network ( id int not null PRIMARY KEY, address cidrnot null, attr1 varchar(10) null ); CREATE TABLE address ( id inetPRIMARY KEY, networkint NOT NULL REFERENCES network ); insert into network( id, address, attr1 ) values( 1, '10.1', 'a' ); insert into network( id, address, attr1 ) values( 2, '10.2', 'b' ); go insert into address( id, network ) values( '10.1.0.1', 1 ); insert into address( id, network ) values( '10.1.0.2', 1 ); insert into address( id, network ) values( '10.1.0.3', 1 ); -- OOPS! insert into address( id, network ) values( '10.2.0.4', 1 ); go -- This will correct the any addresses put in the wrong network update address set network = ( select id from network where address address.id ) [additional network stuff snipped] -- Daryl We want great men who, when fortune frowns, will not be discouraged. -- Colonel Henry Knox, 1776 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Updating cidr column with network operator
[EMAIL PROTECTED] wrote: Am 27.09.2005 um 16:02 schrieb Daryl Richter: An attribute is redundant if it repeats a fact that can be learned without it. If one table contains IP addresses and another contains networks, then you can associate IP addresses and networks with a join of the two tables; indeed, this is how the fix the network column update works. Having a network column in the address table simply repeats what could be learned through the join. I agree with Michael here. I think the fundamental problem with your schema is that it is possible to have contradictory data between the network and address table, always a bad situation. I would replace network.id with a serial type value and make the cidr a separate column, for example: CREATE TABLE network ( id int not null PRIMARY KEY, address cidrnot null, attr1 varchar(10) null ); CREATE TABLE address ( id inetPRIMARY KEY, networkint NOT NULL REFERENCES network ); I agree with Michael too, but I understand him differently: What he says is: Get rid of the redundancy, which means to me: remove the fk from address to network completly. The attribute network is not realy needed because we can always join address.id network.id This reduces the necessary logic to keep things consistent. I still can have my cascaded delete in network, have to do it with a trigger. I'm currently looking at performance issues. Introducing a synthetic pk in network does not really make things easier. Instead I introduced an insert/update trigger which prevents from overlaps in network (which is not as a matter of course for cidr columns, I have learnt): Ok, I guess, but isn't tit true now that you can insert a new address row which doesn't belong to any valid network?? CREATE OR REPLACE FUNCTION dd.ids_network_update_insert_check() RETURNS TRIGGER AS $$ BEGIN -- check if new net overlapps with existing one PERFORM N.id FROM network N WHERE NEW.id N.id OR NEW.id N.id; IF FOUND THEN RAISE EXCEPTION '?Attempt to insert overlapping network %', NEW.id; RETURN NULL; END IF; RETURN NEW; END; $$ LANGUAGE 'plpgsql'; Axel Axel Rau, Frankfurt, Germany +49-69-951418-0 -- Daryl We want great men who, when fortune frowns, will not be discouraged. -- Colonel Henry Knox, 1776 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] how to do 'deep queries'?
Anthony Molinaro wrote: Daryl, Whether you feel that is unnecessary or not, it *is* the ANSI Standard and is thus, by definition, how queries should be written. I disagree 100%. Oracle and db2 introduced window functions years before Ansi added them. Should we not have used them? It absurd to avoid using a feature cuz it's not ansi. Of course it would be absurd, I have not suggested otherwise. Joins are not a *new* feature. Honestly, Don't be a slave to ansi, you miss out on all the great vendor specific functionality *that you're already paying for* it was added to make the *intention* of the query clearer. More clearer to whom? Certainly not developers who have been working for many years using the old syntax. The intention of the old syntax is perfect. Realize that the problem is not the old syntax, the problem is the watered down database field today. I see this more and more with each interview I conduct looking for dba's and developers. I generally agree with your assessment of the state of database knowledge (particularly re developers). It is, however, the reality we live in. [snipped nostalgia and back-patting] I've never worked in a place that used ANSI only syntax and I've never had a problem with clarity nor any developers I've worked with. So, I don't at all get what you're saying... Old style is short and sweet and perfect. Ansi dumbed it down, that's the bottom line. And for people who've been developing for sometime, It's wholly unnecessary. Well, perhaps you will one day and a developer will hose your server with a accidental cross join and then you will understand. But hopefully not. ;) Regards, Anthony [rest snipped] -- Daryl Director of Technology (( Brandywine Asset Management ) ( Expanding the Science of Global Investing ) ( http://www.brandywine.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: [SQL] Updating cidr column with network operator
Axel Rau wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Am 22.09.2005 um 22:26 schrieb Daryl Richter: Axel Rau wrote: Thank you for responding, Daryl, Am 22.09.2005 um 16:45 schrieb Daryl Richter: Axel Rau wrote: Hi SQLers, I have a fk from address to network and try to update the foreign key column to point at the network, it belongs to: CREATE TABLE network ( id cidr PRIMARY KEY , -- 'PK, ,IPv4/6 Network address' ) CREATE TABLE address ( id inet PRIMARY KEY , -- 'PK of IPv4/6 host address' network cidr NOT NULL-- 'FK to Network table' REFERENCES network ON DELETE CASCADE ON UPDATE CASCADE ) I tried (using the WHERE clause to eliminate the addresses were no corresponding net exists): [snip] Networks change during time, being diveded or aggregated or you just enter wrong data during insert. With the UPDATE below, I want to correct the addresses to again point at the right net. While writing this, I learn that because of the pk in network, UPDATEs will be difficult to accomplish (you may need a temporary net to park all addresses of a network to be divided, make the change in network and use the UPDATE below to adjust A.network. I use net '0.0.0.0/32' as 'UNKNOWN' net or for parking. I think if you provide some sample data we can figure this out. Yes, this a goof idea. Playing with small tables let you find quickly the right query. Lets start over with a slightly bigger collection of data: insert into network( id ) values( '10.1/16' ); insert into network( id ) values( '10.2/16' ); insert into network( id ) values( '10.3/16' ); insert into address( id, network ) values( '10.1.0.1', '10.1/16' ); insert into address( id, network ) values( '10.1.0.2', '10.1/16' ); insert into address( id, network ) values( '10.1.0.3', '10.1/16' ); insert into address( id, network ) values( '10.1.0.4', '10.2/16' ); insert into address( id, network ) values( '10.1.0.5', '10.2/16' ); insert into address( id, network ) values( '10.1.0.6', '10.3/16' ); insert into address( id, network ) values( '10.200.0.6', '10.3/16' ); -- address not in network insert into address( id, network ) values( '10.200.0.7', '10.3/16' ); -- address not in network But those are bad inserts, right? I think that I now see what the problem is -- Why do you have a network table at all? It's redundant. If you just insert the ids into your address table, don't the new PostgreSQL operators give you all the information you need? So, for example, if I inserted the data above and then want to answer the question, What are all my 16-bit subnets? select distinct network( set_masklen( id, 16 ) ) from address; network - 10.1.0.0/16 10.2.0.0/16 10.200.0.0/16 [rest snipped] -- Daryl ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Updating cidr column with network operator
Axel Rau wrote: Hi SQLers, I have a fk from address to network and try to update the foreign key column to point at the network, it belongs to: CREATE TABLE network ( id cidr PRIMARY KEY , -- 'PK, ,IPv4/6 Network address' ) CREATE TABLE address ( id inet PRIMARY KEY , -- 'PK of IPv4/6 host address' network cidr NOT NULL-- 'FK to Network table' REFERENCES network ON DELETE CASCADE ON UPDATE CASCADE ) I tried (using the WHERE clause to eliminate the addresses were no corresponding net exists): But you can't insert a row in address w/o a valid network.id? That's what the fk ensures. Perhaps you could elaborate more? Are you trying to *put* on the fk and you currently have bad data? UPDATE address SET network = (SELECT N.id WHERE A.id N.id) FROM address A, network N WHERE A.id N.id; This also makes no sense. For starters, is bitwise shift left ... But this ended up with all network columns pointing at the same net (-:). Any help would be appreciated. Axel Axel Rau, Frankfurt, Germany +49-69-951418-0 ---(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 -- Daryl We want great men who, when fortune frowns, will not be discouraged. -- Colonel Henry Knox, 1776 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Updating cidr column with network operator
Axel Rau wrote: Thank you for responding, Daryl, Am 22.09.2005 um 16:45 schrieb Daryl Richter: Axel Rau wrote: Hi SQLers, I have a fk from address to network and try to update the foreign key column to point at the network, it belongs to: CREATE TABLE network ( id cidr PRIMARY KEY , -- 'PK, ,IPv4/6 Network address' ) CREATE TABLE address ( id inet PRIMARY KEY , -- 'PK of IPv4/6 host address' network cidr NOT NULL-- 'FK to Network table' REFERENCES network ON DELETE CASCADE ON UPDATE CASCADE ) I tried (using the WHERE clause to eliminate the addresses were no corresponding net exists): But you can't insert a row in address w/o a valid network.id? That's what the fk ensures. Perhaps you could elaborate more? Are you trying to *put* on the fk and you currently have bad data? The fk requires a corresponding row in network. But my update tries to reference the right network, that one where the ip address belongs to. I'm still not understanding what you're trying to do, perhaps its a language issue. :) Let me try again. I built your schema and inserted some rows: insert into network( id ) values( '10.1' ); insert into address( id, network ) values( '10.1.0.1', '10.1' ); insert into address( id, network ) values( '10.1.0.2', '10.1' ); insert into address( id, network ) values( '10.1.0.3', '10.1' ); I then select from network: id --- 10.1.0.0/16 and from address: idnetwork --- 10.1.0.1 10.1.0.0/16 10.1.0.2 10.1.0.0/16 10.1.0.3 10.1.0.0/16 Why do you now want to update address.network? They are already pointing to the right network, aren't they? I think if you provide some sample data we can figure this out. UPDATE address SET network = (SELECT N.id WHERE A.id N.id) FROM address A, network N WHERE A.id N.id; This also makes no sense. For starters, is bitwise shift left ... I'm using 8.0.3 and there are some new operators related to inet and cidr data types. On page 157, I found as address/network is contained in network. Finding the net where an address belongs to works as: SELECT id FROM network WHERE inet '$p_ipSource' id; Ahh, ok. see above. Axel Axel Rau, Frankfurt, Germany +49-69-951418-0 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings -- Daryl ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] showing multiple REFERENCE details of id fields in single
Ferindo Middleton Jr wrote: Thanks Daryl. The query you wrote works perfectly for the results I was trying to get at. I modified it a little because the real thing I needed to be able to see is the course_title and not the the id of the courses: SELECT a.course_title AS class_title, c.course_title AS prerequisite_class_title FROM classes a JOINclass_prerequisite_bindings b ON b.class_id = a.id JOINclasses c ON c.id = b.prerequisite WHERE a.id = ?; I don't quite understand how this query is working completely but maybe I'll understand after studying it a little longer. Thank you for your help. Ferindo No problem at all. In future, you should try to place your responses in the body of the reply, it makes it easier for others to read later. Perhaps if you envision it this way? classes.id - class_prerequisite_bindings.id class_prerequisite_bindings.prerequisite - classes.id As a side note, your db schema would be cleaner if you removed the unneeded surrogate key id from the class_prerequisite_bindings table, like so: create table class_prerequisite_bindings( class_id INTEGER REFERENCES classes(id), prerequisite INTEGER REFERENCES classes(id), constraint pk_cpb primary key( class_id, prerequisite ) ); This will, for example, prevent a class from having duplicate prerequisite classes. Good Luck! [snipped] -- Daryl We want great men who, when fortune frowns, will not be discouraged. -- Colonel Henry Knox, 1776 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] showing multiple REFERENCE details of id fields in single
Ferindo Middleton Jr wrote: I have a table which has two id fields which REFERENCE data back at another table. It's setup like this: class_prerequisite_bindings(id SERIAL, class_id INTEGER REFERENCES classes(id), prerequisiteINTEGER REFERENCES classes(id)) The classes table is like this: classes(idSERIAL, course_titleTEXT, course_codeTEXT) I have the following query: SELECT * FROM class_prerequisite_bindings, classes WHERE class_prerequisite_bindings.class_id = 64 AND class_prerequisite_bindings.class_id = classes.id; If I run the query above, the result will only give me info about the class_id field matching id 64 back in the classes table. PROBLEM: I want this query to also show the info about the prerequisite field which would also have info at the classes table. This query will only show the course_title and course_code of the class_id but I need this for the prerequisite field as well. I think I need to do a JOIN, but I don't understand how. How can I do this? create table classes( id SERIAL PRIMARY KEY, course_title TEXT, course_codeTEXT ); go insert into classes( course_title, course_code ) values( 'A', 'A1' ); insert into classes( course_title, course_code ) values( 'B', 'B1' ); insert into classes( course_title, course_code ) values( 'C', 'C1' ); go create table class_prerequisite_bindings( id SERIAL, class_id INTEGER REFERENCES classes(id), prerequisite INTEGER REFERENCES classes(id) ); go insert into class_prerequisite_bindings( class_id, prerequisite ) values( 1, 2 ); insert into class_prerequisite_bindings( class_id, prerequisite ) values( 1, 3 ); go SELECT a.id AS class_id, c.id AS prerequisite_class FROM classes a JOINclass_prerequisite_bindings b ON b.class_id = a.id JOINclasses c ON c.id = b.prerequisite WHERE a.id = 1; go Ferindo ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings -- Daryl We want great men who, when fortune frowns, will not be discouraged. -- Colonel Henry Knox, 1776 ---(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: [SQL] Need help with 'unique parents' constraint
Leif B. Kristensen wrote: On Sunday 11 September 2005 16:04, Greg Sabino Mullane wrote: Not just old-fashioned, it's the biological law! (among homo sapiens anyway). I'd approach this with a trigger, as you can do complex checks and get back nice customized error messages. A sample script follows. Hard to tell without seeing your whole schema, but I see no need for a relation_id primary key if you already have a unique constraint on child_fk and parent_fk, so I made those into the primary key for the relations table: Thank you for an excellent answer. I think I will have to study your code for a while. But is it such a bad idea to have a separate column for the primary key here? I see that there are two schools on this, with diametrically opposed views. For my own part, I feel that it at least doesn't hurt to have a surrogate key. Secondly, a single key value is easier to reference from another table than a composite key. Both are true and as another responder has noted, there are times when surrogate keys are appropriate. Be aware, though, that the real danger is data integrity. Should the alternate key on your composite key get accidentally dropped, invalid data (logical duplicates) can now be inserted. -- Daryl We want great men who, when fortune frowns, will not be discouraged. -- Colonel Henry Knox, 1776 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] insert only if conditions are met?
Henry Ortega wrote: Ok. Here's TABLE A empdate hours type JSMITH 08-15-2005 5 WORK JSMITH 08-15-2005 3 WORK JSMITH 08-25-2005 6 WORK I want to insert the ff: 1.) JSMITH08-15-20058VAC 2.) DOE08-16-20058VAC #1 should fail because there is already 8 hours entered as being Worked on 08-15-2005 (same date). Any suggestions? CREATE TABLE tablea ( emp varchar(6) NOT NULL, datevarchar(10) NOT NULL, hours int NOT NULL, typechar(4) NOT NULL ); grant select, insert, update, delete on tablea to public; insert into tablea( emp, date, hours, type ) values( 'JSMITH', '08-15-2005', 5, 'WORK' ); insert into tablea( emp, date, hours, type ) values( 'JSMITH', '08-15-2005', 3, 'WORK' ); insert into tablea( emp, date, hours, type ) values( 'JSMITH', '08-25-2005', 6, 'WORK' ); create or replace function overtime( varchar, varchar, int, varchar ) returns void as ' insert into tablea( emp, date, hours, type ) select $1, $2, $3, $4 from tablea where ( select sum( hours ) from tablea where emp = $1 and date = $2 group by emp, date ) + $3 = 8 union select $1, $2, $3, $4 from tablea where( select sum( hours ) from tablea where emp = $1 and date = $2 group by emp, date ) is null ' LANGUAGE SQL; select overtime( 'JSMITH', '08-15-2005', 8, 'VAC' );# REJECTED select overtime( 'JSMITH', '08-16-2005', 8, 'VAC' );# OK select overtime( 'JSMITH', '08-25-2005', 2, 'WORK' ); # OK select * from tablea; On 8/31/05, Ragnar Hafstað [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] wrote: On Wed, 2005-08-31 at 11:49 -0400, Henry Ortega wrote: What I am trying to do is * Insert a record for EMPLOYEE A to TABLE A IF the sum of the hours worked by EMPLOYEE A on TABLE A is not equal to N Is this possible? Sure, given a suitable schema It is not clear to me, if the hours worked are to be found in the same table you want to insert into, or not. gnari -- Daryl Richter Platform Author Director of Technology v: 610.361.1000 x202 (( Brandywine Asset Management ) ( Expanding the Science of Global Investing ) ( http://www.brandywine.com )) ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
