Обсуждение: Easy form of "insert if it isn't already there"?

Periodically I find myself wanting to insert into some table,
specifying the primary key column(s), but to simply ignore the request
if it's already there. Currently I have two options:

1) Do the insert as normal, but suppress errors.
SAVEPOINT foo;
INSERT INTO table (col1,col2,col3) VALUES (val1,val2,val3);
(if error) ROLLBACK TO SAVEPOINT foo;

2) Use INSERT... SELECT:
INSERT INTO table (col1,col2,col3) SELECT val1,val2,val3 WHERE NOT
EXISTS (SELECT * FROM table WHERE col1=val1 AND col2=val2)

The former makes unnecessary log entries, the latter feels clunky. Is
there some better way?

All tips appreciated!

Chris Angelico

On Wed, Feb 15, 2012 at 5:26 PM, Bartosz Dmytrak <bdmytrak@eranet.pl> wrote:
> Hi,
> similar topic is in NOVICE mailing
> list: http://archives.postgresql.org/pgsql-novice/2012-02/msg00034.php
>
> e.g. You can use BEGIN... EXCEPTION.... END, good example of
> such approach is
> there: http://www.postgresql.org/docs/9.1/static/plpgsql-control-structures.html#PLPGSQL-UPSERT-EXAMPLE;

Ah, thanks for that!

Currently the query is a single PHP pg_query_params() call, and it's
inside a larger transaction. By the look of it, this requires writing
a function to do the job, rather than embedding the logic straight
into the query - is this correct?

ChrisA

This must be a function or trigger to break one statement into two.  You
could of course simply use two separate statements in PHP as long as
they are in the same transaction.  If you're going to perform this
action in two steps then putting both in a function or trigger is often
preferable.

Looking back at your original question.  Although your option 2 "feels
chunky", it feels to me a generally better option.

INSERT INTO table_name (col1,col2,col3) SELECT val1,val2,val3 WHERE NOT
EXISTS (SELECT * FROM table WHERE col1=val1 AND col2=val2)

or

INSERT INTO table (col1,col2,col3) SELECT val1,val2,val3 WHERE
(val1,val2,val3) NOT IN (SELECT col1,col2,col3 FROM table)

"It does what it says on the tin" and someone else maintaining your code
will understand what it does at a glance.  The same can not be said for
triggers and perhaps functions.  My gut feeling is that the performance
of this will be better too.

Regards,
Phil



On 15/02/2012 07:14, Bartosz Dmytrak wrote:
> Yes it is.
> You can implement trigger on table to check if inserted record is new.
> Still it is on DB side.
> I don't know PHP well enough but I think You can call function e.g.
> SELECT myschema."InsertWhenNew" ("val1", "val2", "val3"); in the same
> way as You call INSERTS
>
> Regards,
> Bartek
>
>
> 2012/2/15 Chris Angelico <rosuav@gmail.com <mailto:rosuav@gmail.com>>
>
>     On Wed, Feb 15, 2012 at 5:26 PM, Bartosz Dmytrak <bdmytrak@eranet.pl
>     <mailto:bdmytrak@eranet.pl>> wrote:
>     > Hi,
>     > similar topic is in NOVICE mailing
>     > list: http://archives.postgresql.org/pgsql-novice/2012-02/msg00034.php
>     >
>     > e.g. You can use BEGIN... EXCEPTION.... END, good example of
>     > such approach is
>     >
>     there: http://www.postgresql.org/docs/9.1/static/plpgsql-control-structures.html#PLPGSQL-UPSERT-EXAMPLE;
>
>     Ah, thanks for that!
>
>     Currently the query is a single PHP pg_query_params() call, and it's
>     inside a larger transaction. By the look of it, this requires writing
>     a function to do the job, rather than embedding the logic straight
>     into the query - is this correct?
>
>     ChrisA
>
>     --
>     Sent via pgsql-general mailing list (pgsql-general@postgresql.org
>     <mailto:pgsql-general@postgresql.org>)
>     To make changes to your subscription:
>     http://www.postgresql.org/mailpref/pgsql-general
>
>

Chris Angelico wrote:
> On Wed, Feb 15, 2012 at 5:26 PM, Bartosz Dmytrak<bdmytrak@eranet.pl>  wrote:
>>
>> e.g. You can use BEGIN... EXCEPTION.... END, good example of
>> such approach is
>> there: http://www.postgresql.org/docs/9.1/static/plpgsql-control-structures.html#PLPGSQL-UPSERT-EXAMPLE;
>

I wonder why, in that example, you would not try the INSERT
first, and if that fails, then do the update?

On 2012-02-15, Chris Angelico <rosuav@gmail.com> wrote:
> Periodically I find myself wanting to insert into some table,
> specifying the primary key column(s), but to simply ignore the request
> if it's already there. Currently I have two options:
>
> 1) Do the insert as normal, but suppress errors.
> SAVEPOINT foo;
> INSERT INTO table (col1,col2,col3) VALUES (val1,val2,val3);
> (if error) ROLLBACK TO SAVEPOINT foo;
>
> 2) Use INSERT... SELECT:
> INSERT INTO table (col1,col2,col3) SELECT val1,val2,val3 WHERE NOT
> EXISTS (SELECT * FROM table WHERE col1=val1 AND col2=val2)
>
> The former makes unnecessary log entries, the latter feels clunky. Is
> there some better way?

neither of those work all of the time.

It's not until the transaction is committed that you can know that it
was successful (ignoring 3-phase for the sake of clarity)

the best way is probably method 2 but remember to handle the errors
that you will still get sometimes.

--
⚂⚃ 100% natural