38412

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

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.

Answer1:

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

Answer2:

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.

Recommend

  • /usr/bin/ld: error: cannot find -lboost_unit_test_framework
  • How can I select the most recent and distinct records using LINQ?
  • RabbitMQ java client stops consuming messages
  • RavenDB indexing errors
  • Should I use composite primary keys in Grails?
  • How can I sync the html/session used between Django's 'Client' and Selenium's we
  • Using Nested Table variables / Collections in SQL inside PL/SQL blocks
  • How can I let users share their location in Bot Framework webchat channel?
  • (Tcl/Expect) clear screen after exit
  • Django return user model id with L
  • How can I tell a form not to dispose a particular control when it closes?
  • SonarQube: Cannot deactivate rule with missing quality profile
  • Tomcat memory Leak
  • Eloquent update method change created_at timestamp
  • Transactional Create with Validation in ServiceStack Redis Client
  • MongoError: Incorrect arguments
  • Row Count Is Returning the incorrect number using RaptureXML
  • Django rest serializer Breaks when data exists
  • Debugging ASP.NET on a built-in web server suddenly stops
  • Illegal mix of collations for operation for date/time comparison
  • PHP - How to update data to MySQL when click a radio button
  • How to redirect a user to a different server and include HTTP basic authentication credentials?
  • Join two tables and save into third-sql
  • How to handle AllServersUnavailable Exception
  • Trying to switch camera back to front but getting exception
  • Release, debug version and Authorization Google?
  • align graphs with different xlab
  • Invalid access key error using credentials redeemed from an amazon open id token
  • Unanticipated behavior
  • retrieve vertices with no linked edge in arangodb
  • Codeigniter doesn't let me update entry, because some fields must be unique
  • python regex in pyparsing
  • Getting error when using KSoap library to consume .NET web services
  • Hits per day in Google Big Query
  • Understanding cpu registers
  • Android Google Maps API OnLocationChanged only called once
  • Add sale price programmatically to product variations
  • Can't mass-assign protected attributes when import data from csv file
  • Unable to use reactive element in my shiny app
  • How can I use threading to 'tick' a timer to be accessed by other threads?