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

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(at)gmail(dot)com <mailto:rosuav(at)gmail(dot)com>>
>
> On Wed, Feb 15, 2012 at 5:26 PM, Bartosz Dmytrak <bdmytrak(at)eranet(dot)pl
> <mailto:bdmytrak(at)eranet(dot)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(at)postgresql(dot)org
> <mailto:pgsql-general(at)postgresql(dot)org>)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
>