0

I'm thinking about switching to PostgreSQL from MySQL. I have no experience with PostgreSQL.

I have a batch update process that naively updates several tables from an Excel workbook using INSERT ... ON DUPLICATE KEY UPDATE.

I've been reading about performing the same thing in PostgreSQL, and I've found that using writable Common Table Expressions are going to be the most efficient, and most "idiomatic" way to go about gaining the same functionality.

I make heavy use of foreign key constraints, and I update junction tables upon the bulk upload.

I don't have the opportunity to make sure that the data is sound, relationship-wise, before it's uploaded. Meaning, I don't check the necessary table for existing keys for the rows in another table that contain them as foreign keys.

Is there a way to capture individual foreign key failures when using a wCTE for upserting many rows into a PostgreSQL database?

To provide a little more information, I'm currently using PHP and looping over a data set, performing a SQL query for every index in the array:

for ($i = 1; $i < $length; $i++)
{
    $values = array(
        $accessoryTypes[$i][0]
        isset($accessoryTypes[$i][1]) ? $accessoryTypes[$i][1] : null
    );

    $query = "INSERT INTO {$table} (`Code`, `Name`) VALUES (?, ?) ON DUPLICATE KEY UPDATE `Name` = VALUES(Name)";
    try
    {
        DB::statement($query, $values);
    } catch (Exception $e)
    {
        $this->sqlErrors['accessoryTypeErrors'][] = $e->getMessage();
    }
}

I catch the exception when the upsert fails - I then later parse the error message and gather which foreign key constraint fails so I can display what the problem was.

I'm wondering if this functionality is possible using CTEs in PostgreSQL. If not - do I have to continue to manually look over the data and perform a separate query for each set when trying to insert it?

Community
  • 1
  • 1
Nathan Lutterman
  • 1,855
  • 4
  • 23
  • 38
  • I'm not sure what you are trying to achieve. `on duplicate key update` wouldn't throw any error, would it? And if you structure the CTE correctly you shouldn't get one either. But if you _do_ get one, the statement would stop at the first error. There is no way of collecting all "wrong" rows and then deal with them later (except for checking them before doing the bulk load in the first place) –  May 07 '14 at 06:12
  • What I'm trying to achieve is a bulk upsert - but if a foreign key constraint fails on an insert or update, I'd like to catch which key failed when uploading. I'm not worried about the primary key. Is this possible when doing an upsert with CTEs, or do I have to manually loop through the data and check my keys beforehand? – Nathan Lutterman May 07 '14 at 06:18
  • The bulk upsert should/would join to the lookup tables so I don't really know what could be failing there. How did you solve this with MySQL and `on duplicate key update`? I don't see the connection there. –  May 07 '14 at 06:23
  • I take the data in as an array and then loop over it, preparing and executing a SQL query that contains `on duplicate key update`. This query contains data that has a foreign key - if the insert fails because the foreign key constraint fails, I catch the exception and check the constraint that failed. I can do this because I'm doing a separate query for every index in the array. Can I do something similar if I use a CTE? – Nathan Lutterman May 07 '14 at 06:28
  • The usual way to do such a thing is to load the raw data into a staging table. Then do a bulk upsert (using a CTE) from the staging table into the real tables. Inside the CTE you would join to the lookup tables, so you don't get a FK violation. No need for a slow row-by-row processing over an array. –  May 07 '14 at 06:31
  • Forgive me, I'm not quite understanding. If the CTE tries to insert a row into a database and refers to a nonexistent foreign key, won't it fail? Just for a little more context - the foreign keys are unique varchars in some cases. – Nathan Lutterman May 07 '14 at 06:38
  • If you _join_ to the FK tables then it won't (but it won't insert anything either). But a CTE is no different from any other statement. It throws the same errors. So if you can handle a FK error with your current INSERT statement you should be able to handle that error with a CTE just as well. –  May 07 '14 at 06:40
  • Ah - alright, I understand what you're saying now. I'd like to actually have it throw an error when it tries to insert a nonexistent foreign key, instead of just ignoring it. I'll just have to test it, I suppose. – Nathan Lutterman May 07 '14 at 06:44
  • 1
    As I said: the error thrown from a CTE is no different than the one thrown from a plain `insert. –  May 07 '14 at 06:46
  • 1
    Maybe worth to mention, that if you are running all of the inserts (or *upserts*) in a transaction, PostgreSQL will ignore anything after the first error. Failed transactions needs to be rolled back (at least to a savepoint). – pozs May 07 '14 at 08:12

0 Answers0