[SQL] increment the primary key value without using sequences
Hi all, I would like to know if its possible to increment the primary key field value of stocks table without using sequences by getting the next value of the pk field from the sequnce table ? insert into stocks (prim_id, date,time, code, price, volume) (select . from temp_stocks) sequence sequence_code | next_value prim_id 101 stocks table (prim_id, date, time, code, price, volume) temp_stocks table (date, time, code, price, volume) If it can be done, can you please explain me with an example. Thanks a lot in advance. - Sick sense of humor? Visit Yahoo! TV's Comedy with an Edge to see what's on, when.
Re: [SQL] raise exception and transaction handling
Hi, Marcin I'm not familiar with PREPARE TRANSACTION maby it could be done in that way. But, you can use a EXCEPTION clause, and there put your insert. IF EXISTS (SELECT 1 FROM g.m_lista WHERE idf = NEW.id) THEN RAISE EXCEPTION 'CENY NIE SPELNIAJA WARUNKOW! %', rtrim(bledne); END IF; EXCEPTION when RAISE_EXCEPTION then INSERT INTO g.m_proba VALUES (1,2); END; It will work corect if you have only one RAISE EXCEPTION,becouse RAISE_EXCEPTION concern every exception called by RAISE command. I hope that will help. - Origi nal Message - From: Marcin Krawczyk To: pgsql-sql@postgresql.org Sent: Saturday, July 28, 2007 10:54 PM Subject: [SQL] raise exception and transaction handling Hi, I have a problem with transaction handling. What I need to do is execute an INSERT command that would not be canceled by the RAISE EXCEPTION command in AFTER UPDATE TRIGGER. A piece of code: BEGIN -- some computations bledne := (SELECT g.q_sumka('Poz.' || lps || ' - min. cena: ' || cena || ' ' || waluta ||'; ') FROM g.m_lista WHERE idf = NEW.id); IF EXISTS (SELECT 1 FROM g.m_lista WHERE idf = NEW.id) THEN RAISE EXCEPTION 'CENY NIE SPELNIAJA WARUNKOW! %', rtrim(bledne); BEGIN INSERT INTO g.m_proba VALUES (1,2); -- this is the operation I need to perform but the RAISE EXCEPTION above cancels it out PREPARE TRANSACTION 'a'; COMMIT PREPARED 'a'; END; END IF; I tried to do it as shown above, with PREPARE and COMMIT but it's not working.
Re: [SQL] increment the primary key value without using sequences
On Jul 29, 2007, at 7:00 , roopa perumalraja wrote: I would like to know if its possible to increment the primary key field value of stocks table without using sequences by getting the next value of the pk field from the sequnce table ? Why? Any other solution will cause performance issues. Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[SQL] Migration from SQLite Help (Left Join)
SELECT c.customer_id as customer_id,c.customer_number as customer_number, c.customer_name as customer_name,c.customer_status as customer_status,cat.category_name as category_name, c.bill_state as bill_state, coalesce(ctots.balance_due, 0.00) as balance FROM customers as c, customer_categories as cat left join (Select customer_id as cid, coalesce(sum(balance_due),0) as balance_due FROM invoice_master WHERE status = 'Pending' group by cid) ctots on ctots.cid = c.customer_id where cat.category_id = c.category_id AND customer_name LIKE lower('%%') AND (c.customer_status = 'Terminated' OR c.customer_status = 'Active' or c.customer_status = 'Inactive') ORDER BY c.customer_number DESC LIMIT 25 I know, it's a beast, but I'm porting an application from SQLite to PostgreSQL and this is the only query that isn't working properly in PostgreSQL. The error is "ERROR: invalid reference to FROM-clause entry for table "c" Hint: There is an entry for table "c", but it cannot be referenced from this part of the query. Character: 475" - it's the "on ctots.cid = c.customer_id " part that's breaking. Is there any way to accomplish the same thing in PG? -- - Mitchell Vincent - K Software - Innovative Software Solutions - Visit our website and check out our great software! - http://www.ksoftware.net ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] Migration from SQLite Help (Left Join)
Mitchell Vincent skrev: > SELECT c.customer_id as customer_id,c.customer_number as customer_number, > c.customer_name as customer_name,c.customer_status as > customer_status,cat.category_name as category_name, > c.bill_state as bill_state, coalesce(ctots.balance_due, 0.00) as > balance FROM customers as c, > customer_categories as cat > left join > (Select customer_id as cid, coalesce(sum(balance_due),0) as balance_due > FROM invoice_master WHERE status = 'Pending' group by cid) ctots on > ctots.cid = c.customer_id > where cat.category_id = c.category_id AND customer_name > LIKE lower('%%') AND (c.customer_status = 'Terminated' OR > c.customer_status = 'Active' or c.customer_status = 'Inactive') > ORDER BY c.customer_number DESC LIMIT 25 The problem seems to be that you expect SELECT a FROM b,c LEFT JOIN d to be interpreted as SELECT a FROM (b CROSS JOIN c) LEFT JOIN d whereas it is translated by postgresql as SELECT a FROM b CROSS JOIN (c LEFT JOIN d) There are many ways to fix this - I would suggest moving the join condition into the FROM-clause: SELECT c.customer_id as customer_id,c.customer_number as customer_number, c.customer_name as customer_name,c.customer_status as customer_status,cat.category_name as category_name, c.bill_state as bill_state, coalesce(ctots.balance_due, 0.00) as balance FROM customers as c INNER JOIN customer_categories as cat ON cat.category_id = c.category_id LEFT JOIN (Select customer_id as cid, coalesce(sum(balance_due),0) as balance_due FROM invoice_master WHERE status = 'Pending' group by cid) ctots on ctots.cid = c.customer_id WHERE customer_name LIKE lower('%%') AND (c.customer_status = 'Terminated' OR c.customer_status = 'Active' or c.customer_status = 'Inactive') ORDER BY c.customer_number DESC LIMIT 25 In fact, I believe you could remove the subquery as well: SELECT c.customer_id as customer_id,c.customer_number as customer_number, c.customer_name as customer_name,c.customer_status as customer_status,cat.category_name as category_name, c.bill_state as bill_state, coalesce(sum(im.balance_due, 0.00) as balance FROM customers as c INNER JOIN customer_categories as cat ON cat.category_id = c.category_id LEFT JOIN invoice_master im ON im.status = 'Pending' AND im.cid = c.customer_id WHERE customer_name LIKE lower('%%') AND (c.customer_status = 'Terminated' OR c.customer_status = 'Active' or c.customer_status = 'Inactive') ORDER BY c.customer_number DESC LIMIT 25 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq