using conditional logic : check if record exists; if it does, update it, if not, create it [duplicate]

Question

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.

you should either use a SQL stored procedure or either find out if the user exists in php — feco
– feco, Commented Jul 19, 2012 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
– Erwin Brandstetter, Commented Jul 19, 2012 at 23:26

Community · Accepted Answer · 2017-05-23 12:20:06Z

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

edited May 23, 2017 at 12:20

CommunityBot

11 silver badge

answered Jul 19, 2012 at 23:27

cegfault

6,6523 gold badges30 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

1252748 Over a year ago

I think that's smart. Since this is just a quick fix until table structures can be improved. 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!

cegfault Over a year ago

I don't have time right now to give a better answer, but basically just look into conditional expressions in PostgreSQL (hint: one of your conditions is NULL)

1252748 Over a year ago

:( okay. I am in ovet my head, but i will give it a shot! Thanks!

ryuusenshi · Accepted Answer · 2012-07-20 00:57:03Z

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.

answered Jul 20, 2012 at 0:57

ryuusenshi

1,98613 silver badges15 bronze badges

Collectives™ on Stack Overflow

using conditional logic : check if record exists; if it does, update it, if not, create it [duplicate]

2 Answers 2

3 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Linked

Related