I asked this last night, and got information on merging (which is unavailable in postgresql). I'm willing to try the workaround suggested But I'm just trying to understand why it can't be done with conditional logic.
I've clarified the question a bit, so maybe this will be easier to understand.
I have a query that inserts data into a table. But it is creating a new record every time. Is there a way I can check if the row is there first, then if it is, UPDATE, and if it isn't INSERT?
$user = 'username';
$timestamp = date('Y-m-d G:i:s.u');
$check_time = "start"; //can also be stop
$check_type = "start_user"; //can also be stop_user
$insert_query = "INSERT INTO production_order_process_log (
production_order_id, production_order_process_id, $check_time, $check_type)
VALUES (
'$production_order_id', '$production_order_process_id', '$timestamp', '$user')";
The idea is that the table will record check-in and check-out values (production_order_process_log.start and production_order_process_log.stop). So before a record with a check-out time stamp is made, the query should check to see if the $production_order_process_id already exists. if it does exist, then the timestamp can go into stop and the $check_type can be stop_user. Otherwise, they can stay start and start_user.
I am basically trying to avoid this result.
+----+---------------------+--------------------------------+--------------------+-------------------+-------------+-------------+
| id | production_order_id | production_order_process_id | start | stop | start_user | stop_user |
+----+---------------------+--------------------------------+--------------------+-------------------+-------------+-------------+
| 8 | 2343 | 1000 | 12 july 03:23:23 | NULL | tlh | NULL |
+----+---------------------+--------------------------------+--------------------+-------------------+-------------+-------------+
| 9 | 2343 | 1000 | NULL | 12 july 03:45:00 | NULL | tlh |
+----+---------------------+--------------------------------+--------------------+-------------------+-------------+-------------+
Many thanks for helping me suss out the postgresql logic to do this task.
This question and answer will be of interest to you: Insert, on duplicate update in PostgreSQL?
Basically, either use two queries (do the select, if it's found update, otherwise insert), which is not the best solution (two scripts running simultaneously could give duplicate inserts), or do as the above questions suggests - make a stored procedure/function to do it (this is probably the best option, and easiest).
Recognizing the nature of your workflow, it seems that an order can not stop before or at the same time as it starts, right? And it had to have started in order to stop, right? Please correct me if I'm wrong.
So you could just check whether it's a start operation and do an INSERT in that case, or stop operation and do an UPDATE.
I feel like concurrency doesn't really come into play here.