[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
