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

> 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.

Sweet GOD, I hope no one ever has to read the stuff I'm working on!

This is just a personal curiousity project, not anything for work or
anything released publicly. I'd be far too embarrassed to ever release
this, as I'm just fumbling along, learning. :)

> 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.

Concurrency shouldn't be an issue - this is a perl script running from
cron. The only concurrency that will ever happen is if I'm a bonehead
and I run the script manually right as cron kicks off another copy.

> 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.

Well, I was hoping to minimize the amount of perl needed to get this one
task done, but I think that's going to be the best way to do it.

> 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"...

Yes indeed, good catch. I'll clean that up immediately.

Thanks, Greg!

Benny

--
"Even if a man chops off your hand with a sword, you still have two nice,
sharp bones to stick in his eyes."
-- .sig on Slashdot