Thread: how to overwrite tuples in a table
Hi,
I have a table pointattributes(pointid,attributeid,strvalue,numvalue)......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/static/sql-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: > Hi, > > I have a table > pointattributes(pointid,attributeid,strvalue,numvalue)......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 >
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@mail.nih.gov] Sent: Thursday, September 09, 2004 7:55 PM To: Pradeepkumar, Pyatalo (IE10) Cc: pgsql-novice@postgresql.org Subject: Re: [NOVICE] how to overwrite tuples in a table Are you looking for UPDATE? http://www.postgresql.org/docs/7.4/static/sql-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: > Hi, > > I have a table > pointattributes(pointid,attributeid,strvalue,numvalue)......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 >
"Pradeepkumar, Pyatalo (IE10)" wrote:
> 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.
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
Oliver, > (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 ...) 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
Josh Berkus wrote: >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. > > > All, 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