Postgresql查询检查id是否存在，如果不存在则会插入（临时）并且如果存在则回滚全部

Question

I'm just a beginner with programming

I want to know if anyone have solution for my problem (PostgreSQL & Yii2)

I have one button to submit these array data (for this case just 5 rows):

+----+--------+
| id |  name  |
+----+--------+
| 1  | apple  |
| 2  | orange |
| 3  | manggo |
| 1  | fish   |
| 4  | meat   |
+----+--------+

1. if id=1 not exist, insert record1 ( or to temp tbl )
2. if id=2 not exist, insert record2 ( or to temp tbl )
3. if id=3 not exist, insert record3 ( or to temp tbl )
4. if id=1 not exist, insert record2 ( this one failed, cancel all 1,2,3 and stop )

Every step using 2 queries (SELECT COUNT & INSERT)

+----+--------+
| id |  name  |
+----+--------+
| 1  | apple  |
| 2  | orange |
| 3  | manggo |
| 4  | fish   |
| 5  | meat   |
+----+--------+

For second case after all proceed (until id=5 no record exist) commit to database

Thank you

Answer 1

Make sure the id column has a unique constraint, assuming your id column is the primary key it should already have this. Open a transaction and then just insert the rows, if the id already exists the DB will throw an error. If an error is thrown catch it and perform a rollback, otherwise commit the transaction.

Example:

$data = [
    ['id' => 1, 'name' => 'Item 1'],
    ['id' => 2, 'name' => 'Item 2'],
    ['id' => 3, 'name' => 'Item 3'],
    ['id' => 4, 'name' => 'Item 4'],
    ['id' => 5, 'name' => 'Item 5'],
];

$connection = Yii::$app->db;
$transaction = $connection->beginTransaction();
try {
    foreach ($data as $item) {
        $connection->createCommand()->insert('{{%your_table}}', $item)->execute();
    }
    $transaction->commit();
} catch (\yii\db\IntegrityException $exception) {
    $transaction->rollBack();
}

I don't know how important it is for you to insert the id value yourself, but you might be better of using a serial for your id column.