Thread: mysql replace in postgreSQL?
In MySQL, I can use the replace statement which either updates the data there or inserts it. Is there a comporable syntax to use in postgreSQL? I need to do an insert and don't want to have to worry about if the data is already there or not...so don't want to see if it there, if so do update if not insert...etc. Thanks.
blackwater dev wrote: >In MySQL, I can use the replace statement which either updates the >data there or inserts it. Is there a comporable syntax to use in >postgreSQL? > >I need to do an insert and don't want to have to worry about if the >data is already there or not...so don't want to see if it there, if so >do update if not insert...etc. > > > Use a trigger. Sincerely, Joshua D. Drake >Thanks. > >---------------------------(end of broadcast)--------------------------- >TIP 6: explain analyze is your friend > > -- The PostgreSQL Company - Command Prompt, Inc. 1.503.667.4564 PostgreSQL Replication, Consulting, Custom Development, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/
blackwater dev wrote: > In MySQL, I can use the replace statement which either updates the > data there or inserts it. Is there a comporable syntax to use in > postgreSQL? > > I need to do an insert and don't want to have to worry about if the > data is already there or not...so don't want to see if it there, if so > do update if not insert...etc. > > Thanks. > In Oracle this is called the MERGE statement, but it not yet in pg. It is on the TODO list, though.
blackwater dev wrote:
>In MySQL, I can use the replace statement which either updates the
>data there or inserts it. Is there a comporable syntax to use in
>postgreSQL?
>
>I need to do an insert and don't want to have to worry about if the
>data is already there or not...so don't want to see if it there, if so
>do update if not insert...etc.
>
>
look this functions..
is not general solution..
CREATE TYPE mydata AS (
f1 integer ,
f2 integer,
);
CREATE OR REPLACE FUNCTION updatefoo(data mydata, myid bigint) RETURNS
boolean AS $$
DECLARE
BEGIN
update foo_table set
f1 = mydata.f1,
f2 = mydata.f2
WHERE id = myid;
IF NOT FOUND THEN
return false;
END IF;
return true;
END
$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION insertfoo(data mydata, myid bigint) RETURNS
boolean AS $$
DECLARE
rep boolean DEFAULT false;
BEGIN
insert into foo_table (
id ,
f1,
f2
) values (
mydata.id,
mydata.f1,
mydata.f2
);
return rep;
END
$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION replaceFoo(data mydata, myid bigint) RETURNS
boolean AS $$
DECLARE
rep boolean = false;
BEGIN
rep = updatefoo(mydata,myid );
if not rep then
rep = insertfoo(mydata,myid );
end if;
return rep;
END
$$ LANGUAGE plpgsql;
On 10/29/2005, "blackwater dev" <blackwaterdev@gmail.com> wrote:
>In MySQL, I can use the replace statement which either updates the
>data there or inserts it. Is there a comporable syntax to use in
>postgreSQL?
>
>I need to do an insert and don't want to have to worry about if the
>data is already there or not...so don't want to see if it there, if so
>do update if not insert...etc.
>
>Thanks.
>
>---------------------------(end of broadcast)---------------------------
>TIP 6: explain analyze is your friend
BEGIN;
-- other operations
SAVEPOINT sp1;
INSERT INTO wines VALUES('Chateau Lafite 2003', '24');
-- Assume the above fails because of a unique key violation,
-- so now we issue these commands:
ROLLBACK TO sp1;
UPDATE wines SET stock = stock + 24 WHERE winename = 'Chateau Lafite
2003';
-- continue with other operations, and eventually
COMMIT;
(extracted from the PostgreSQL Manual -
http://www.postgresql.org/docs/8.0/interactive/sql-update.html -)
On Fri, Oct 28, 2005 at 09:57:03PM -0400, blackwater dev wrote: > In MySQL, I can use the replace statement which either updates the > data there or inserts it. Is there a comporable syntax to use in > postgreSQL? Not really, but here's an example which doesn't have the brokenness of MySQL's REPLACE INTO and doesn't have the race conditions that some others' proposals have. http://developer.postgresql.org/docs/postgres/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING Of course, it's not as nice and flexible as the SQL standard MERGE, but until that day comes, you can use that example. HTH :) Cheers, D -- David Fetter david@fetter.org http://fetter.org/ phone: +1 510 893 6100 mobile: +1 415 235 3778 Remember to vote!
Am Sonntag, den 30.10.2005, 06:29 -0800 schrieb David Fetter: > On Fri, Oct 28, 2005 at 09:57:03PM -0400, blackwater dev wrote: > > In MySQL, I can use the replace statement which either updates the > > data there or inserts it. Is there a comporable syntax to use in > > postgreSQL? > > Not really, but here's an example which doesn't have the brokenness of > MySQL's REPLACE INTO and doesn't have the race conditions that some > others' proposals have. > > http://developer.postgresql.org/docs/postgres/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING > > Of course, it's not as nice and flexible as the SQL standard MERGE, > but until that day comes, you can use that example. In most cases, just DELETE and then INSERT should work perfectly. (UPDATE and MERGE would cause dead tuples in the same way so in the end they are only syntactical sugar) Another way is a rule for insert which turns it into an update in case the desired tuple is already existent.
On Sun, Oct 30, 2005 at 03:52:23PM +0100, Tino Wildenhain wrote: > Am Sonntag, den 30.10.2005, 06:29 -0800 schrieb David Fetter: > > On Fri, Oct 28, 2005 at 09:57:03PM -0400, blackwater dev wrote: > > > In MySQL, I can use the replace statement which either updates > > > the data there or inserts it. Is there a comporable syntax to > > > use in postgreSQL? > > > > Not really, but here's an example which doesn't have the > > brokenness of MySQL's REPLACE INTO and doesn't have the race > > conditions that some others' proposals have. > > > > http://developer.postgresql.org/docs/postgres/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING > > > > Of course, it's not as nice and flexible as the SQL standard > > MERGE, but until that day comes, you can use that example. > > In most cases, just DELETE and then INSERT should work perfectly. > (UPDATE and MERGE would cause dead tuples in the same way so in the > end they are only syntactical sugar) > > Another way is a rule for insert which turns it into an update in > case the desired tuple is already existent. That has a race condition in it. What happens if something deletes the tuple between the attempted INSERT and the UPDATE? Cheers, D -- David Fetter david@fetter.org http://fetter.org/ phone: +1 510 893 6100 mobile: +1 415 235 3778 Remember to vote!
At 06:29 AM 10/30/2005 -0800, David Fetter wrote: >On Fri, Oct 28, 2005 at 09:57:03PM -0400, blackwater dev wrote: > > In MySQL, I can use the replace statement which either updates the > > data there or inserts it. Is there a comporable syntax to use in > > postgreSQL? > >Not really, but here's an example which doesn't have the brokenness of >MySQL's REPLACE INTO and doesn't have the race conditions that some >others' proposals have. > >http://developer.postgresql.org/docs/postgres/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING Erm, doesn't it have the same race conditions? It's just fine as long as you have an appropriate uniqueness constraint. If you have the appropriate uniqueness constraint, you'll be fine whatever you do as long as you're not doing something too stupid. If it is possible to lock on something that already exists or has "yet to exist" then maybe you can do such an insert/update. e.g. "SELECT .... FOR INSERT WHERE field1=x" How about customizable user locking? e.g. lock string "tablename field1=x" Anyway, I used to lock tables before doing selects before inserts/updates. Link.
On Sun, Oct 30, 2005 at 11:47:41PM +0800, Lincoln Yeoh wrote: > At 06:29 AM 10/30/2005 -0800, David Fetter wrote: > > >On Fri, Oct 28, 2005 at 09:57:03PM -0400, blackwater dev wrote: > >> In MySQL, I can use the replace statement which either updates > >> the data there or inserts it. Is there a comporable syntax to > >> use in postgreSQL? > > > >Not really, but here's an example which doesn't have the brokenness > >of MySQL's REPLACE INTO and doesn't have the race conditions that > >some others' proposals have. > > > >http://developer.postgresql.org/docs/postgres/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING > > Erm, doesn't it have the same race conditions? No, don't believe it does. Have you found some? > If you have the appropriate uniqueness constraint, you'll be fine > whatever you do as long as you're not doing something too stupid. > > If it is possible to lock on something that already exists or has > "yet to exist" then maybe you can do such an insert/update. > > e.g. "SELECT .... FOR INSERT WHERE field1=x" > > How about customizable user locking? e.g. lock string "tablename > field1=x" > > Anyway, I used to lock tables before doing selects before > inserts/updates. That's a real performance killer. Cheers, D -- David Fetter david@fetter.org http://fetter.org/ phone: +1 510 893 6100 mobile: +1 415 235 3778 Remember to vote!
At 08:24 AM 10/30/2005 -0800, David Fetter wrote: > > >http://developer.postgresql.org/docs/postgres/plpgsql-control-structure > s.html#PLPGSQL-ERROR-TRAPPING > > > > Erm, doesn't it have the same race conditions? > >No, don't believe it does. Have you found some? Depends on how you do things. As I mentioned, it's only fine if you have the relevant uniqueness constraint. For example, if instead of: CREATE TABLE db (a INT PRIMARY KEY, b TEXT); You do: CREATE TABLE db (a INT, b TEXT); Then create the function as in the docs. Then open up two psql sessions. Session #1 begin; SELECT merge_db(1, 'david'); select * from db; Session #2 begin; SELECT merge_db(1, 'dennis'); select * from db; Then, do commit in both sessions. You'll end up with duplicates. What actually protected the data before was the uniqueness constraint due to the primary key. BUT, if you already have the uniqueness constraint, you won't have a problem mis-inserting duplicates with any of the typical naive methods either. Not saying the example in the docs is wrong, but it might be misleading to people who don't fully understand it. That doesn't seem too far fetched to me given that a fair number here have suggested the usual variations of "select, update if exist insert if not" and assumed they will work. Would those methods be fine in non MVCC databases? Regards, Link.
On 10/31/2005 11:58 AM, Lincoln Yeoh wrote: > At 08:24 AM 10/30/2005 -0800, David Fetter wrote: > >> > >http://developer.postgresql.org/docs/postgres/plpgsql-control-structure >> s.html#PLPGSQL-ERROR-TRAPPING >> > >> > Erm, doesn't it have the same race conditions? >> >>No, don't believe it does. Have you found some? > > Depends on how you do things. > > As I mentioned, it's only fine if you have the relevant uniqueness constraint. One would use MySQL's REPLACE INTO to avoid duplicates. To deliberately omit the UNIQUE constraint in order to make the stored procedure solution fail would smell a lot like the old MySQL crashme BS ... first create and drop 10,000 tables to bloat the system catalog, next vacuum with a user that doesn't have privileges to vacuum system catalogs (because we told them to vacuum after that silly crap test), then show that the system is still slow. Using REPLACE INTO at one place and creating duplicates on purpose in another seems to make zero sense to me. Until one can explain the reason for that to me, I claim that a UNIQUE constraint on such key is a logical consequence. Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com #
Am Sonntag, den 30.10.2005, 07:24 -0800 schrieb David Fetter: ... > > In most cases, just DELETE and then INSERT should work perfectly. > > (UPDATE and MERGE would cause dead tuples in the same way so in the > > end they are only syntactical sugar) > > > > Another way is a rule for insert which turns it into an update in > > case the desired tuple is already existent. > > That has a race condition in it. What happens if something deletes > the tuple between the attempted INSERT and the UPDATE? How so in a transaction?
without unique constraint even mysql replace doesnot work as expected Jan Wieck wrote: > On 10/31/2005 11:58 AM, Lincoln Yeoh wrote: > >> At 08:24 AM 10/30/2005 -0800, David Fetter wrote: >> >>> > >>> >http://developer.postgresql.org/docs/postgres/plpgsql-control-structure >>> s.html#PLPGSQL-ERROR-TRAPPING >>> > >>> > Erm, doesn't it have the same race conditions? >>> >>> No, don't believe it does. Have you found some? >> >> >> Depends on how you do things. >> >> As I mentioned, it's only fine if you have the relevant uniqueness >> constraint. > > > One would use MySQL's REPLACE INTO to avoid duplicates. To deliberately > omit the UNIQUE constraint in order to make the stored procedure > solution fail would smell a lot like the old MySQL crashme BS ... first > create and drop 10,000 tables to bloat the system catalog, next vacuum > with a user that doesn't have privileges to vacuum system catalogs > (because we told them to vacuum after that silly crap test), then show > that the system is still slow. > > Using REPLACE INTO at one place and creating duplicates on purpose in > another seems to make zero sense to me. Until one can explain the reason > for that to me, I claim that a UNIQUE constraint on such key is a > logical consequence. > > > Jan >
At 12:28 AM 11/2/2005 -0500, Jan Wieck wrote: >Using REPLACE INTO at one place and creating duplicates on purpose in >another seems to make zero sense to me. Until one can explain the reason >for that to me, I claim that a UNIQUE constraint on such key is a logical >consequence. I believe it is better to tell people to use UNIQUE constraints to avoid duplicates than to tell them to use a particular stored procedure. I was just pointing out that the "magic" wasn't really in the stored procedure. Especially since that particular stored procedure does not generalize easily - you have to change it to use it on another table. Users might make mistakes of using the procedure on a table without a uniqueness constraint in the right fields, or the wrong uniqueness constraint (e.g. different collation from the one they use in a select). Whereas if they had a REPLACE/PUT/MERGE with similar syntax as an UPDATE, that is less likely to increase the possibility of errors. Regards, Link.