Thanks all. I tried the appended code in a trigger
function, but postgresql won't take it.
It complains that assets.quantity is not a scalar.
However, the WHERE clause in that select statement
guarantees that at most only one record will be
returned. An open position on a given kind of asset
On Dec 7, 2007, at 11:42 AM, Colin Wetherbee wrote:
You can do this with a conditional. Something like the following
should work.
IF
NOT (a query matching your data returns rows)
THEN
INSERT (your new data)
There exists a race condition here unless you've locked your tables.
Vivek Khera wrote:
On Dec 7, 2007, at 11:42 AM, Colin Wetherbee wrote:
You can do this with a conditional. Something like the following
should work.
IF
NOT (a query matching your data returns rows)
THEN
INSERT (your new data)
There exists a race condition here unless you've locked your
On Dec 10, 2007, at 5:04 PM, Colin Wetherbee wrote:
For what it's worth, the real algorithm would be as follows. I
hadn't had enough coffee yet, and I forgot the UPDATE bit.
IF
(a query matching your old data returns rows)
THEN
UPDATE with your new data
ELSE
INSERT your new data
Still
--- Vivek Khera [EMAIL PROTECTED] wrote:
On Dec 10, 2007, at 5:04 PM, Colin Wetherbee wrote:
For what it's worth, the real algorithm would be
as follows. I
hadn't had enough coffee yet, and I forgot the
UPDATE bit.
IF
(a query matching your old data returns rows)
THEN
On Dec 10, 2007, at 4:48 PM, Ted Byers wrote:
--- Vivek Khera [EMAIL PROTECTED] wrote:
On Dec 10, 2007, at 5:04 PM, Colin Wetherbee wrote:
For what it's worth, the real algorithm would be
as follows. I
hadn't had enough coffee yet, and I forgot the
UPDATE bit.
IF
(a query matching
Thanks Erik
In a stored procedure you'd just execute the UPDATE
and then check
the FOUND variable to see if it found a row to
update:
UPDATE table_name SET foo='bar' WHERE id=5;
IF NOT FOUND THEN
INSERT INTO table_name (id, foo) VALUES (5, 'bar');
END IF;
To be clear, if I
--- On Mon, 12/10/07, Ted Byers [EMAIL PROTECTED] wrote:
but how do you
do it using SQL in an RDBMS?
I believe that there is an ANSI SQL command MERGE that is yet to be
implemented into PostgreSQL.
Regards,
Richard Broersma Jr.
---(end of
Richard Broersma Jr wrote:
--- On Mon, 12/10/07, Ted Byers [EMAIL PROTECTED] wrote:
but how do you
do it using SQL in an RDBMS?
I believe that there is an ANSI SQL command MERGE that is yet to be
implemented into PostgreSQL.
IIRC the standard's definition of MERGE is still subject
Vivek Khera wrote:
On Dec 10, 2007, at 5:04 PM, Colin Wetherbee wrote:
For what it's worth, the real algorithm would be as follows. I
hadn't had enough coffee yet, and I forgot the UPDATE bit.
IF (a query matching your old data returns rows) THEN UPDATE with
your new data ELSE INSERT your
On 12/10/07, Colin Wetherbee [EMAIL PROTECTED] wrote:
Vivek Khera wrote:
On Dec 10, 2007, at 5:04 PM, Colin Wetherbee wrote:
IF (a query matching your old data returns rows) THEN UPDATE with
your new data ELSE INSERT your new data
Still exists race condition. Your race comes from
So two design patterns for a makeshift UPSERT have been presented -
one is to check beforehand, and only insert if the item isn't present
already, the other is to do the insert blindly and let PG check for
you, and catch any exceptions.
I'm also wondering what people's ideas are for a sort
On Dec 8, 2007, at 7:54 AM, John D. Burger wrote:
So two design patterns for a makeshift UPSERT have been presented -
one is to check beforehand, and only insert if the item isn't
present already
... which will give the wrong results if there's any concurrent
updates...
, the other is
--- Erik Jones [EMAIL PROTECTED] wrote:
On Dec 6, 2007, at 2:36 PM, Ted Byers wrote:
[snip]
What you want to do here for handling the update v.
insert is called
an UPSERT. Basically, what you do is run the
update as if the row
exists and catch the exception that is thrown if it
On Dec 7, 2007, at 6:29 AM, Ted Byers wrote:
--- Erik Jones [EMAIL PROTECTED] wrote:
On Dec 6, 2007, at 2:36 PM, Ted Byers wrote:
[snip]
What you want to do here for handling the update v.
insert is called
an UPSERT. Basically, what you do is run the
update as if the row
exists and catch
Ted Byers wrote:
--- Erik Jones [EMAIL PROTECTED] wrote:
On Dec 6, 2007, at 2:36 PM, Ted Byers wrote:
[snip]
What you want to do here for handling the update v.
insert is called
an UPSERT. Basically, what you do is run the
update as if the row
exists and catch the exception that is
On Dec 7, 2007, at 8:29 AM, Ted Byers wrote:
--- Erik Jones [EMAIL PROTECTED] wrote:
On Dec 6, 2007, at 2:36 PM, Ted Byers wrote:
[snip]
What you want to do here for handling the update v.
insert is called
an UPSERT. Basically, what you do is run the
update as if the row
exists and catch
IS there such a thing? I can be the first to consider
this.
What I am aiming for is a solution with a couple
coupled tables, one of which represents state through
time and the other represents transactions or deltas
on the state. With one field (a floating point
number) in the state table (or
--- Ted Byers [EMAIL PROTECTED] wrote:
IS there such a thing? I can be the first to
consider
this.
OOPS. The mind is faster than the fingers. That
should have been I can NOT be the first to consider
this.
Ted
---(end of broadcast)---
TIP
On Dec 6, 2007, at 2:36 PM, Ted Byers wrote:
IS there such a thing? I can be the first to consider
this.
What I am aiming for is a solution with a couple
coupled tables, one of which represents state through
time and the other represents transactions or deltas
on the state. With one field
20 matches
Mail list logo
