Thread: Implementing replace function
Hello Postgres users, to mimic the MySQL-REPLACE statement I need to try to UPDATE a record and if that fails - INSERT it. But how can I detect that the UPDATE has failed in my SQL procedure? begin transaction; create table pref_users ( id varchar(32) primary key, first_name varchar(32), last_name varchar(32), female boolean, avatar varchar(128), city varchar(32), lat real check (-90 <= lat and lat <= 90), lng real check (-90 <= lng and lng <= 90), last_login timestamp default current_timestamp, last_ip inet, medals smallint check (medals > 0) ); create table pref_rate ( obj varchar(32) references pref_users(id), subj varchar(32) references pref_users(id), good boolean, fair boolean, nice boolean, about varchar(256), last_rated timestamp default current_timestamp ); create table pref_money ( id varchar(32) references pref_users, yw char(7) default to_char(current_timestamp, 'YYYY-WW'), money real ); create index pref_money_yw_index on pref_money(yw); create or replace function update_pref_users(id varchar, first_name varchar, last_name varchar, female boolean, avatar varchar, city varchar, last_ip inet) returns void as $$ update pref_users set first_name = $2, last_name = $3, female = $4, avatar = $5, city = $6, last_ip = $7 where id = $1; -- XXX how to detect failure here? XXX insert into pref_users(id, first_name, last_name, female, avatar, city, last_ip) values ($1, $2, $3, $4, $5, $6, $7); $$ language sql; commit; Thank you Alex
Hello 2010/10/31 Alexander Farber <alexander.farber@gmail.com>: > Hello Postgres users, > > to mimic the MySQL-REPLACE statement I need > to try to UPDATE a record and if that fails - INSERT it. > > But how can I detect that the UPDATE has failed in my SQL procedure? > see: http://www.postgresql.org/docs/8.4/interactive/plpgsql-control-structures.html near to end of page Regards Pavel Stehule > begin transaction; > > create table pref_users ( > id varchar(32) primary key, > first_name varchar(32), > last_name varchar(32), > female boolean, > avatar varchar(128), > city varchar(32), > lat real check (-90 <= lat and lat <= 90), > lng real check (-90 <= lng and lng <= 90), > last_login timestamp default current_timestamp, > last_ip inet, > medals smallint check (medals > 0) > ); > > create table pref_rate ( > obj varchar(32) references pref_users(id), > subj varchar(32) references pref_users(id), > good boolean, > fair boolean, > nice boolean, > about varchar(256), > last_rated timestamp default current_timestamp > ); > > create table pref_money ( > id varchar(32) references pref_users, > yw char(7) default to_char(current_timestamp, 'YYYY-WW'), > money real > ); > create index pref_money_yw_index on pref_money(yw); > > create or replace function update_pref_users(id varchar, > first_name varchar, last_name varchar, female boolean, > avatar varchar, city varchar, last_ip inet) returns void as $$ > > update pref_users set > first_name = $2, > last_name = $3, > female = $4, > avatar = $5, > city = $6, > last_ip = $7 > where id = $1; > > -- XXX how to detect failure here? XXX > > insert into pref_users(id, first_name, last_name, > female, avatar, city, last_ip) > values ($1, $2, $3, $4, $5, $6, $7); > $$ language sql; > > commit; > > Thank you > Alex > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
Thanks Pavel, but I have an SQL procedure and not plpgsql? On Sun, Oct 31, 2010 at 9:34 AM, Pavel Stehule <pavel.stehule@gmail.com> wrote: >> But how can I detect that the UPDATE has failed in my SQL procedure? >> > > see: http://www.postgresql.org/docs/8.4/interactive/plpgsql-control-structures.html > near to end of page >> create or replace function update_pref_users(id varchar, >> first_name varchar, last_name varchar, female boolean, >> avatar varchar, city varchar, last_ip inet) returns void as $$ >> >> update pref_users set >> first_name = $2, >> last_name = $3, >> female = $4, >> avatar = $5, >> city = $6, >> last_ip = $7 >> where id = $1; >> >> -- XXX how to detect failure here? XXX >> >> insert into pref_users(id, first_name, last_name, >> female, avatar, city, last_ip) >> values ($1, $2, $3, $4, $5, $6, $7); >> $$ language sql;
2010/10/31 Alexander Farber <alexander.farber@gmail.com>: > Thanks Pavel, but I have an SQL procedure and not plpgsql? it's not possible in sql. But plpgsql is same level like stored procedures language from mysql. > > On Sun, Oct 31, 2010 at 9:34 AM, Pavel Stehule <pavel.stehule@gmail.com> wrote: >>> But how can I detect that the UPDATE has failed in my SQL procedure? >>> >> >> see: http://www.postgresql.org/docs/8.4/interactive/plpgsql-control-structures.html >> near to end of page > >>> create or replace function update_pref_users(id varchar, >>> first_name varchar, last_name varchar, female boolean, >>> avatar varchar, city varchar, last_ip inet) returns void as $$ >>> >>> update pref_users set >>> first_name = $2, >>> last_name = $3, >>> female = $4, >>> avatar = $5, >>> city = $6, >>> last_ip = $7 >>> where id = $1; >>> >>> -- XXX how to detect failure here? XXX >>> >>> insert into pref_users(id, first_name, last_name, >>> female, avatar, city, last_ip) >>> values ($1, $2, $3, $4, $5, $6, $7); >>> $$ language sql; > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
Hey Alexander, Pavel
The solution like below should works IMO, but it does not.
insert into pref_users(id, first_name, last_name,
female, avatar, city, last_ip)
select $1, $2, $3, $4, $5, $6, $7
where not exists
(update pref_users set first_name = $2,
last_name = $3,
female = $4,
avatar = $5,
city = $6,
last_ip = $7
where id = $1
returning id);
BTW, I don't understand why it not possible to write query like this:
SELECT id FROM (UPDATE test SET nm = 'dima' WHERE id = 1 RETURNING id) AS foo;
According to the doc (of UPDATE command) "The syntax of the RETURNING list is identical to
that of the output list of SELECT).
With this syntax, the OPs goal can be implemented in SQL..
--
// Dmitriy.
The solution like below should works IMO, but it does not.
insert into pref_users(id, first_name, last_name,
female, avatar, city, last_ip)
select $1, $2, $3, $4, $5, $6, $7
where not exists
(update pref_users set first_name = $2,
last_name = $3,
female = $4,
avatar = $5,
city = $6,
last_ip = $7
where id = $1
returning id);
BTW, I don't understand why it not possible to write query like this:
SELECT id FROM (UPDATE test SET nm = 'dima' WHERE id = 1 RETURNING id) AS foo;
According to the doc (of UPDATE command) "The syntax of the RETURNING list is identical to
that of the output list of SELECT).
With this syntax, the OPs goal can be implemented in SQL..
--
// Dmitriy.
Hello 2010/10/31 Dmitriy Igrishin <dmitigr@gmail.com>: > Hey Alexander, Pavel > > The solution like below should works IMO, but it does not. > insert into pref_users(id, first_name, last_name, > female, avatar, city, last_ip) > select $1, $2, $3, $4, $5, $6, $7 > where not exists > (update pref_users set first_name = $2, > last_name = $3, > female = $4, > avatar = $5, > city = $6, > last_ip = $7 > where id = $1 > returning id); > > BTW, I don't understand why it not possible to write query like this: > SELECT id FROM (UPDATE test SET nm = 'dima' WHERE id = 1 RETURNING id) AS > foo; > According to the doc (of UPDATE command) "The syntax of the RETURNING list > is identical to > that of the output list of SELECT). > With this syntax, the OPs goal can be implemented in SQL.. > UPDATE RETURNING isn't subselect - so you can't do SELECT FROM (UPDATE RETURNING) directly. It's possible with wrapping to sql function. In next pg version 9.1 you can do it via Updatable Common Table Expression, but it isn't possible in older version. Regards Pavel Stehule > -- > // Dmitriy. > > >
Okay, Pavel, will wait for 9.1 :-)
It is a common case - insert new row if it cannot be updated.
--
// Dmitriy.
It is a common case - insert new row if it cannot be updated.
2010/10/31 Pavel Stehule <pavel.stehule@gmail.com>
Hello
2010/10/31 Dmitriy Igrishin <dmitigr@gmail.com>:UPDATE RETURNING isn't subselect - so you can't do SELECT FROM (UPDATE> Hey Alexander, Pavel
>
> The solution like below should works IMO, but it does not.
> insert into pref_users(id, first_name, last_name,
> female, avatar, city, last_ip)
> select $1, $2, $3, $4, $5, $6, $7
> where not exists
> (update pref_users set first_name = $2,
> last_name = $3,
> female = $4,
> avatar = $5,
> city = $6,
> last_ip = $7
> where id = $1
> returning id);
>
> BTW, I don't understand why it not possible to write query like this:
> SELECT id FROM (UPDATE test SET nm = 'dima' WHERE id = 1 RETURNING id) AS
> foo;
> According to the doc (of UPDATE command) "The syntax of the RETURNING list
> is identical to
> that of the output list of SELECT).
> With this syntax, the OPs goal can be implemented in SQL..
>
RETURNING) directly. It's possible with wrapping to sql function.
In next pg version 9.1 you can do it via Updatable Common Table
Expression, but it isn't possible in older version.
Regards
Pavel Stehule
> --
> // Dmitriy.
>
>
>
--
// Dmitriy.
2010/10/31 Dmitriy Igrishin <dmitigr@gmail.com>: > Okay, Pavel, will wait for 9.1 :-) > > It is a common case - insert new row if it cannot be updated. you can find (probably) MERGE statement in 9.1. Pavel > > 2010/10/31 Pavel Stehule <pavel.stehule@gmail.com> >> >> Hello >> >> 2010/10/31 Dmitriy Igrishin <dmitigr@gmail.com>: >> > Hey Alexander, Pavel >> > >> > The solution like below should works IMO, but it does not. >> > insert into pref_users(id, first_name, last_name, >> > female, avatar, city, last_ip) >> > select $1, $2, $3, $4, $5, $6, $7 >> > where not exists >> > (update pref_users set first_name = $2, >> > last_name = $3, >> > female = $4, >> > avatar = $5, >> > city = $6, >> > last_ip = $7 >> > where id = $1 >> > returning id); >> > >> > BTW, I don't understand why it not possible to write query like this: >> > SELECT id FROM (UPDATE test SET nm = 'dima' WHERE id = 1 RETURNING id) >> > AS >> > foo; >> > According to the doc (of UPDATE command) "The syntax of the RETURNING >> > list >> > is identical to >> > that of the output list of SELECT). >> > With this syntax, the OPs goal can be implemented in SQL.. >> > >> >> UPDATE RETURNING isn't subselect - so you can't do SELECT FROM (UPDATE >> RETURNING) directly. It's possible with wrapping to sql function. >> >> In next pg version 9.1 you can do it via Updatable Common Table >> Expression, but it isn't possible in older version. >> >> Regards >> >> Pavel Stehule >> >> > -- >> > // Dmitriy. >> > >> > >> > > > > > -- > // Dmitriy. > > >
Alexander Farber wrote on 31.10.2010 09:22: > Hello Postgres users, > > to mimic the MySQL-REPLACE statement I need > to try to UPDATE a record and if that fails - INSERT it. > There is actually an example of this in the PG manual ;) http://www.postgresql.org/docs/current/static/plpgsql-control-structures.html#PLPGSQL-UPSERT-EXAMPLE Here is another solution based on triggers: http://database-programmer.blogspot.com/2009/06/approaches-to-upsert.html Regards Thomas
Thanks for all the comments. Do I need to use BEGIN/COMMIT in my plpgsql-function for UPSERT or are functions atomic? Regards Alex
And would a pure SQL-function solution to call an INSERT followed by an UPDATE in its body and ignoring the error? (don't know how ignore it best though, so that I don't ignore other critical errors)
[corrected typo, sorry] And wouldn't a pure SQL-function solution be: to call an INSERT followed by an UPDATE in its body and ignoring the error? (don't know how ignore that error best though, so that I don't ignore other critical errors)
2010/10/31 Alexander Farber <alexander.farber@gmail.com>: > Thanks for all the comments. > > Do I need to use BEGIN/COMMIT in my plpgsql-function for UPSERT or are > functions atomic? If you use a code from documentation, then you don't need explicit transaction - every SQL run inside outer implicit transaction in PostgreSQL. Pavel > > Regards > Alex > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
2010/10/31 Alexander Farber <alexander.farber@gmail.com>: > And would a pure SQL-function solution to call > an INSERT followed by an UPDATE in its body > and ignoring the error? (don't know how ignore it > best though, so that I don't ignore other critical errors) > You must not ignore errors in SQL - it's not T-SQL :) Pavel > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
I've created a function now (below) and can call it as well, but how can I see it at the psql prompt? Is there a \d command for that or should I dump the database to see my declarations? And is my function atomic? I.e. can't it happen, that FOUND is not true, but then another session calls a INSERT inbetween? pref=> create or replace function pref_update_users(_id varchar, pref(> _first_name varchar, _last_name varchar, _female boolean, pref(> _avatar varchar, _city varchar, _last_ip inet) returns void as $BODY$ pref$> BEGIN pref$> pref$> update pref_users set pref$> first_name = _first_name, pref$> last_name = _last_name, pref$> female = _female, pref$> avatar = _avatar, pref$> city = _city, pref$> last_ip = _last_ip pref$> where id = _id; pref$> pref$> IF NOT FOUND THEN pref$> insert into pref_users(id, first_name, pref$> last_name, female, avatar, city, last_ip) pref$> values (_id, _first_name, _last_name, pref$> _female, _avatar, _city, _last_ip); pref$> END IF; pref$> END; pref$> $BODY$ language plpgsql; CREATE FUNCTION Thanks and merry Halloween Alex
\df *update* Alexander Farber wrote: > I've created a function now (below) and can call it as well, > but how can I see it at the psql prompt? Is there a \d command > for that or should I dump the database to see my declarations? > > And is my function atomic? I.e. can't it happen, that FOUND > is not true, but then another session calls a INSERT inbetween? > > pref=> create or replace function pref_update_users(_id varchar, > pref(> _first_name varchar, _last_name varchar, _female boolean, > pref(> _avatar varchar, _city varchar, _last_ip inet) > returns void as $BODY$ > pref$> BEGIN > pref$> > pref$> update pref_users set > pref$> first_name = _first_name, > pref$> last_name = _last_name, > pref$> female = _female, > pref$> avatar = _avatar, > pref$> city = _city, > pref$> last_ip = _last_ip > pref$> where id = _id; > pref$> > pref$> IF NOT FOUND THEN > pref$> insert into pref_users(id, first_name, > pref$> last_name, female, avatar, city, last_ip) > pref$> values (_id, _first_name, _last_name, > pref$> _female, _avatar, _city, _last_ip); > pref$> END IF; > pref$> END; > pref$> $BODY$ language plpgsql; > CREATE FUNCTION > > Thanks and merry Halloween > Alex > >
On 31/10/2010 17:28, Alexander Farber wrote: > I've created a function now (below) and can call it as well, > but how can I see it at the psql prompt? Is there a \d command > for that or should I dump the database to see my declarations? You can do \df public.*, assuming that your function is in the public schema, to get a list of all functions; and \df+ <function name> will give you the function body also. \? is your friend here. > And is my function atomic? I.e. can't it happen, that FOUND > is not true, but then another session calls a INSERT inbetween? Yes - all functions are executed in an implicit transaction. Ray. -- Raymond O'Donnell :: Galway :: Ireland rod@iol.ie
Hello, If you expect your data to reach some kind of "critical size" at some point ( ie updates will be more likely than inserts at that point ), you can optimize your UPSERT code by trying to UPDATE before INSERTing. Otherwise trying to INSERT first should decrease the average UPSERT execution time in the long run, since you are less likely to hit the exception and do some extra work on the table. Anyway, glad to hear this is going to be accounted for in 9.1 . :) Le 31/10/2010 11:07, Thomas Kellerer a écrit : > Alexander Farber wrote on 31.10.2010 09:22: >> Hello Postgres users, >> >> to mimic the MySQL-REPLACE statement I need >> to try to UPDATE a record and if that fails - INSERT it. >> > > There is actually an example of this in the PG manual ;) > > http://www.postgresql.org/docs/current/static/plpgsql-control-structures.html#PLPGSQL-UPSERT-EXAMPLE > > > Here is another solution based on triggers: > http://database-programmer.blogspot.com/2009/06/approaches-to-upsert.html > > Regards > Thomas > >
On Tue, Nov 02, 2010 at 10:46:42AM +0100, Matthieu Huin wrote: > Hello, > > If you expect your data to reach some kind of "critical size" at some > point ( ie updates will be more likely than inserts at that point ), you > can optimize your UPSERT code by trying to UPDATE before INSERTing. > Otherwise trying to INSERT first should decrease the average UPSERT > execution time in the long run, since you are less likely to hit the > exception and do some extra work on the table. You'd almost think of using some kind of branch prediction techniques. You could track what happened the last two times and use that to predict which would be better. There's always pathelogical cases, but it could work well for normal workloads. Have a nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Patriotism is when love of your own people comes first; nationalism, > when hate for people other than your own comes first. > - Charles de Gaulle