Thread: insert on duplicate update?
hi, is there in postgres anything similar to: insert .... on duplicate update? here is the problem i need a solution for: I need to insert in pg_type a row which was previously selected from there and with slight modifications, the problem is that when i try to insert the row there might be a row with the same 'typname' which is sort of primary key. The second problem is that when doing those operations i don't want to depend on the specific structure of the pg_type catalog. I want my procedure to work even if in some future release additional columns to pg_type are added. So what i want is to insert a row in a table that was previously selected from the same table but if there is primary key collision i want to update the row that collided. And all these without being dependant on the table structure. -- Using Opera's revolutionary e-mail client: http://www.opera.com/m2/
On Wed, Nov 09, 2005 at 01:31:59PM +0200, Peter Filipov wrote: > hi, > is there in postgres anything similar to: > insert .... on duplicate update? > here is the problem i need a solution for: > I need to insert in pg_type a row which was previously selected from there > and <snip> What's wrong with CREATE TYPE? Have a nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them.
Attachment
Peter Filipov wrote: > hi, > is there in postgres anything similar to: > insert .... on duplicate update? > here is the problem i need a solution for: > I need to insert in pg_type a row which was previously selected from > there and > with slight modifications, the problem is that when i try to insert the row > there might be a row with the same 'typname' which is sort of primary key. > The second problem is that when doing those operations i don't want to > depend > on the specific structure of the pg_type catalog. Are you certain it will always be safe to do this? How will you provide meaningful values for columns you don't know exist? -- Richard Huxton Archonet Ltd
Richard Huxton <dev@archonet.com> writes:
> Peter Filipov wrote:
>> is there in postgres anything similar to:
>> insert .... on duplicate update?
>> here is the problem i need a solution for:
>> I need to insert in pg_type a row which was previously selected from
>> there and
>> with slight modifications, the problem is that when i try to insert the row
>> there might be a row with the same 'typname' which is sort of primary key.
>> The second problem is that when doing those operations i don't want to
>> depend
>> on the specific structure of the pg_type catalog.
> Are you certain it will always be safe to do this?
What makes you think that modifying pg_type directly is a sane idea in
the first place? When (not if) it breaks, you'll not get a lot of sympathy.
regards, tom lane
You absolutely should NOT I repeat NOT -- DON'T dink around with the system catalogs in ANY database... If you need to create UDT's then you should follow the proper mechanisms to create your own user-defined types... ""Peter Filipov"" <pfilipov@netissat.bg> wrote in message news:opszy0zlg8ligf9v@draco... > hi, > is there in postgres anything similar to: > insert .... on duplicate update? > here is the problem i need a solution for: > I need to insert in pg_type a row which was previously selected from there > and > with slight modifications, the problem is that when i try to insert the > row > there might be a row with the same 'typname' which is sort of primary key. > The second problem is that when doing those operations i don't want to > depend > on the specific structure of the pg_type catalog. I want my procedure > to work even if in some future release additional columns to pg_type are > added. > So what i want is to insert a row in a table that was previously selected > from the same > table but if there is primary key collision i want to update the row that > collided. And > all these without being dependant on the table structure. > > -- > Using Opera's revolutionary e-mail client: http://www.opera.com/m2/ > > ---------------------------(end of broadcast)--------------------------- > TIP 3: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq >