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.