2

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.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
1252748
  • 14,597
  • 32
  • 109
  • 229
  • you should either use a SQL stored procedure or either find out if the user exists in php – feco Jul 19 '12 at 22:33
  • You may have clarified the question, but I still fail to see what you are trying to do. You use `$production_order_id` but it is not defined. After having read the comprehensive blog post by @Depesz there should not be much left to ask ... – Erwin Brandstetter Jul 19 '12 at 23:26
  • @ErwinBrandstetter Fair enough. sounds good. – 1252748 Jul 19 '12 at 23:32

2 Answers2

2

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).

Community
  • 1
  • 1
cegfault
  • 6,442
  • 3
  • 27
  • 49
  • I think that's smart. Since this is just a quick fix until table structures can be improved. http://ideone.com/nhAlE i am still curious how to check for the `NULL` values and replace them as indicated in the large comment in the ideone link. Any ideas on that. Thank you. Your idea is too obvious to see at first (for someone like me anyway), and therefore brilliant! – 1252748 Jul 20 '12 at 01:39
  • I don't have time right now to give a better answer, but basically just look into [conditional expressions](http://www.postgresql.org/docs/8.4/static/functions-conditional.html) in PostgreSQL (hint: one of your conditions is `NULL`) – cegfault Jul 20 '12 at 01:42
  • :( okay. I am in ovet my head, but i will give it a shot! Thanks! – 1252748 Jul 20 '12 at 01:45
1

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.

ryuusenshi
  • 1,976
  • 13
  • 15