Message-ID: <56362.63.227.74.41.1097813923.squirrel@63.227.74.41>
Date: Thu, 14 Oct 2004 23:18:21 -0500 (CDT)
Subject: Inserting into table only if the row does not already exist.
From: "C. Bensend" <benny@bennyvision.com>
To: pgsql-sql@postgresql.org
User-Agent: SquirrelMail/1.4.3a
MIME-Version: 1.0
Content-Type: text/plain;charset=iso-8859-1
Content-Transfer-Encoding: 8bit
Importance: Normal


Hey folks,

   I am inserting data into a table with a three-column primary key (the
table is only three columns).  The rows I'm trying to insert may very
well be duplicates of ones already in the table, so I would like to have
PostgreSQL handle the insert and possible error resulting from dup data.
I can certainly do a SELECT first and then INSERT if it's not a duplicate,
ala:

* Do a SELECT against the three columns
* If there are no rows returned, then do the INSERT

   But I thought this would be possible with the following:

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.

   Is this possible to do in one statement?  I checked with a DBA friend
(he's an Oracle guy), Google, and the list archives, and either didn't
find anything helpful or simply didn't try the correct search terms.  Or
should I be doing this sort of thing in two separate queries?

Thanks for all the help you folks have given me,

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