Hi,

I have a table
pointattributes(pointid,attributeid,strvalue,numva lue)......where pointid
and attributeid are foreign keys refering to other tables. Now how can i
overwrite tuples in this table. To make it simpler,
pointid attributeid strvalue numvalue
----------------------------------------------------------------------
4 45 hello 3
7 50 hai 9

I want this table to retain the latest data.....
so if i say insert into pointattributes values(7,50,'Good Morning',10), it
should overwrite the previous tuple with this latest one. Is there a way to
do that.
One simple way is to check if there are any tuples with the given pointid
and attributeid, if a tuple exists delete the tuple with the pointid and
attribute(the combination is unique) and then insert the new tuple.
But is there any better way to do that.

Thanks in advance.


With Best Regards
Pradeep Kumar P J

Are you looking for UPDATE?

http://www.postgresql.org/docs/7.4/s...ql-update.html

Sean

UPDATE pointattributes SET strvalue='Good Morning', numvalue=9 WHERE
pointid=7 AND attributeid=50;

On Sep 9, 2004, at 10:13 AM, Pradeepkumar, Pyatalo (IE10) wrote:


---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

No I am not looking for UPDATE command.For updating first i should check if
there is a tuple with the given pointid and attributeid, if present then i
should use the UPDATE command.....thats fine. But what I wanted was a
situation wherein, I will blindly call the insert command and the database
takes care of overwriting the previous values.....is that possible.


-----Original Message-----
From: Sean Davis [mailto:sdavis2 (AT) mail (DOT) nih.gov]
Sent: Thursday, September 09, 2004 7:55 PM
To: Pradeepkumar, Pyatalo (IE10)
Cc: pgsql-novice (AT) postgresql (DOT) org
Subject: Re: [NOVICE] how to overwrite tuples in a table


Are you looking for UPDATE?

http://www.postgresql.org/docs/7.4/s...ql-update.html

Sean

UPDATE pointattributes SET strvalue='Good Morning', numvalue=9 WHERE
pointid=7 AND attributeid=50;

On Sep 9, 2004, at 10:13 AM, Pradeepkumar, Pyatalo (IE10) wrote:

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

"Pradeepkumar, Pyatalo (IE10)" wrote:
I suggest you just try the UPDATE command, and if that
fails, perform an INSERT. You have to lock the table,
in case another process is trying to do the same thing
at the same time.

(Unfortunately, you cannot use a transaction, because
the failing UPDATE would abort the transaction. However,
I believe the nested-transaction feature of PostgreSQL 8
(still in beta) would allow to do such things. Someone
please correct me if I'm wrong ...)

Best regards
Oliver

--
Oliver Fromme, secnetix GmbH & Co KG, Oettingenstr. 2, 80538 München
Any opinions expressed in this message may be personal to the author
and may not necessarily reflect the opinions of secnetix in any way.

"I made up the term 'object-oriented', and I can tell you
I didn't have C++ in mind."
-- Alan Kay, OOPSLA '97

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Oliver,

You are correct. However, he can do it right now the other way around, if
it can be nested into a plpgsql function or done with libpq: do an UPDATE,
check the number of rows affected, and if it's 0, do an insert.

By 8.1/8.2 we'll likely have implemented the new SQL spec for this sort of
operation, and this common problem will go away.

Mind you, it's not a problem I've ever personally had. I'm actually a bit
puzzled about how the application could NOT know whether it's handling a new
or a modified row; makes me wonder about people's application design.

--
Josh Berkus
Aglio Database Solutions
San Francisco

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Josh Berkus wrote:


This is a very timely thread! I was just thinking about the exact same
thing. As for why we would want to do that, I'll explain my situation.

I have an old BASIC compiler/interpreter that uses AlphaMicro compatible
ISAM databases. I have a customer that has for the past 10 years been
writing modules into this system, and it runs pretty much every aspect
of their business. Now they would really like to be able to access the
data from outside of the BASIC environment (ODBC access would be
wonderful). This customer owns both the source code for the BASIC
interpreter & compiler and the business sysytem.

During my stint here I have been porting the system to Linux and fixing
some obscure bugs in the ISAM section. After figuring out how the ISAM
subsystem works, I am thinking that I can hook into both the WRITE block
and ISAM delete parts, pass the data off to another process, and have it
insert or delete data from the relational database. This way I'd be able
to have a read-only mirror of the data in a relational database for
customers to access. The problem is, I can't (easily/reliably)
differentiate the difference between a WRITE to a new block (an insert)
or an existing one (update), hence my exact same problem as the original
poster.

Unfortunately I have only really ever done simple things in SQL
database, so I'm kinda green.

Dumb question: could the plpgsql function that you mentioned be
implemented as a trigger as to remain transparent to my bridge?

Thanks,
Tim


---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match