Re: Inserting into table only if the row does not already exist.

"C. Bensend" <benny(at)bennyvision(dot)com> writes:

> INSERT INTO table ( column1, column2, column3 )
> SELECT column1, column2, column3
> WHERE NOT EXISTS (
> SELECT column1, column2, column3 FROM table WHERE
> column1 = $column1 AND
> column2 = $column2 AND
> column3 = $column3 )
>
> .. which gave me 'ERROR: column1 does not exist'. Nuts.

Well you're not selecting from any table so "column1" isn't going to exist.
You just have to put it in the select list as a constant. If you're feeling
generous to the next programmer to read it you could put "AS column1" after
each one, but the column name doesn't actually have to match the column you're
inserting into.

INSERT INTO table ( column1, column2, column3 )
(
SELECT $column1, $column2, $column3
WHERE NOT EXISTS (
SELECT 1
FROM table
WHERE column1 = $column1
AND column2 = $column2
AND column3 = $column3 )
)

Note that this is going to have some concurrency issues. I think it will be
possible for a second query to execute before the first commits. In that case
it won't see the record the first query inserted and try to insert again.
You'll just get a primary key violation though which I guess you can just
ignore.

Which raises a question. Why not forgoe this complicated SQL and try to do the
insert. If you get a primary key violation, well there's your answer... If you
don't care about the failure just ignore it and move on. I would suggest
checking specifically for a primary key violation and still stopping execution
on unexpected errors though.

If you're doing this inside a bigger transaction that's a bit more of a pain.
Until 8.0 postgres can't handle ignoring an error on a query without aborting
the entire transaction. But if you're in autocommit mode then you can just
ignore the primary key violation and continue.

Incidentally, if you're putting your parameters directly into your queries
using $column1 then you've got a potential security problem. Unless you're
quoting every variable everywhere religiously using postgres's quoting
functions an attacker can sneak extra SQL into your queries. Potentially
including whole new statements such as "DELETE FROM table"...

--
greg