Easy form of "insert if it isn't already there"?

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
;

Regards,
Bartek

2012/2/15 Chris Angelico <rosuav@gmail.com>

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

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>

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?

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

Maybe to show how "found" works and how to ignore errors - that is my
assumption only.

Regards,
Bartek

2012/2/15 Berend Tober <btober@broadstripe.net>

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