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.
>
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
executi
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
\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
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 INSER
2010/10/31 Alexander Farber :
> 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 :)
Pav
2010/10/31 Alexander Farber :
> 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
[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)
--
Sent via pgsql-general mailing list (pgsql-general@pos
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)
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your sub
Thanks for all the comments.
Do I need to use BEGIN/COMMIT in my plpgsql-function for UPSERT or are
functions atomic?
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
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-struct
2010/10/31 Dmitriy Igrishin :
> 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
>>
>> Hello
>>
>> 2010/10/31 Dmitriy Igrishin :
>> > Hey Alexander, Pavel
>> >
Okay, Pavel, will wait for 9.1 :-)
It is a common case - insert new row if it cannot be updated.
2010/10/31 Pavel Stehule
> Hello
>
> 2010/10/31 Dmitriy Igrishin :
> > Hey Alexander, Pavel
> >
> > The solution like below should works IMO, but it does not.
> > insert into pref_users(id, first_
Hello
2010/10/31 Dmitriy Igrishin :
> 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 p
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,
l
2010/10/31 Alexander Farber :
> 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
> wrote:
>>> But how can I detect that the UPDATE has fai
Thanks Pavel, but I have an SQL procedure and not plpgsql?
On Sun, Oct 31, 2010 at 9:34 AM, Pavel Stehule 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 p
Hello
2010/10/31 Alexander Farber :
> 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/pl
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
19 matches
Mail list logo
