mysql replace in postgreSQL?

[blackwater dev]

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

[Joshua D. Drake]

Post by blackwater dev
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

Post by blackwater dev
Thanks.
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

[Bricklen Anderson]

Post by blackwater dev
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.

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

[Kostas Maistrelis]

Post by blackwater dev
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;


---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

[c***@clickdiario.com]

Post by blackwater dev
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 -)

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

[David Fetter]

Post by blackwater dev
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 ***@fetter.org http://fetter.org/
phone: [phone] mobile: [phone]

Remember to vote!

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ***@postgresql.org so that your
message can get through to the mailing list cleanly

[Tino Wildenhain]

Post by David Fetter

Post by blackwater dev
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.



---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

[David Fetter]

Post by Tino Wildenhain

Post by David Fetter

Post by blackwater dev
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

[Tino Wildenhain]

Am Sonntag, den 30.10.2005, 07:24 -0800 schrieb David Fetter:
...

Post by David Fetter

Post by Tino Wildenhain
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?



---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

[Lincoln Yeoh]

Post by David Fetter

Post by blackwater dev
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.







---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ***@postgresql.org so that your
message can get through to the mailing list cleanly

[David Fetter]

Post by Lincoln Yeoh

Post by David Fetter

Post by blackwater dev
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?

Post by Lincoln Yeoh
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 ***@fetter.org http://fetter.org/
phone: [phone] mobile: [phone]

Remember to vote!

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

[Lincoln Yeoh]

Post by David Fetter

Post by Lincoln Yeoh

Post by David Fetter
http://developer.postgresql.org/docs/postgres/plpgsql-control-structure

s.html#PLPGSQL-ERROR-TRAPPING

Post by Lincoln Yeoh
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.


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

[Jan Wieck]

Post by Lincoln Yeoh

Post by David Fetter

Post by Lincoln Yeoh

Post by David Fetter
http://developer.postgresql.org/docs/postgres/plpgsql-control-structure

s.html#PLPGSQL-ERROR-TRAPPING

Post by Lincoln Yeoh
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. #
#================================================== ***@Yahoo.com #

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

[Tzvetan Tzankov]

without unique constraint even mysql replace doesnot work as expected

Post by Jan Wieck

Post by Lincoln Yeoh

Post by David Fetter

Post by David Fetter
http://developer.postgresql.org/docs/postgres/plpgsql-control-structure

s.html#PLPGSQL-ERROR-TRAPPING

Post by David Fetter
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

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

[Lincoln Yeoh]

Post by Jan Wieck
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.



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

http://www.postgresql.org/docs/faq