UPDATE/INSERT based on if a row exists

Question

Working on some postgreSQL queries. As I said in a previous question..my knowledge of SQL logic is quite limited..

I have this query that inserts a row.

$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')
";

Unfortunately, that is adding a new row every time. I would like to add conditional SQL so that

if the production_order_process_id doesn't exist, do the INSERT as it's written in the query above. That is, add the new row with all the new information

but if the production_order_process_id does exist and the check_type is stop_user then UPDATE the row to fill the column stop with the $timestamp and fill the column stop_user with $user.

I understand this is complicated.. Or, at least for me it is ^^ Thanks much for the help!

The standard SQL would be `MERGE`. But I don't think Postgre supports it? Try http://stackoverflow.com/questions/1109061/insert-on-duplicate-update-postgresql — podiluska, Jul 19 '12 at 11:03
@podiluska very quick google makes me think maybe not as well :( is there any other way? I will look into a postgresql `MERGE` or substitutes more.. — 1252748, Jul 19 '12 at 11:05
Duplicate question: http://stackoverflow.com/questions/1109061/insert-on-duplicate-update-postgresql — , Jul 19 '12 at 11:12

score 2 · Accepted Answer · answered Jul 19 '12 at 13:55

2

This is usually called MERGE or upsert. PostgreSQL doesn't have explicit support for this operation.

The best article I've seen on the topic of MERGE in PostgreSQL is this one by depesz .

answered Jul 19 '12 at 13:55

Craig Ringer

307,061
76
688
778

Why is merge better than using conditional logic to test if the fields are NULL? Thanks! – 1252748 Jul 19 '12 at 21:00
So that if `start`i is `NULL` i put `$timestamp` into it, but if it has a value, i put it into `stop`… – 1252748 Jul 19 '12 at 21:04
@thomas Concurrency, basically. Think about what an concurrent change to the record you are examining will do. You can use a `SELECT ... FOR UPDATE` if to avoid that if you are careful but it won't work in all circumstances. – Craig Ringer Jul 20 '12 at 02:04

user1259132 · Answer 2 · 2012-07-19T11:11:37.773

It would be good if you can create a stored procedure and call while insert new record.

    DELIMITER $$

    DROP PROCEDURE IF EXISTS `DB`.`InsertNewRow` $$
    CREATE PROCEDURE `db`.`InsertNewRow` ()
    BEGIN

    DECLARE V_EXIST INT DEFAULT 0;
    DECLARE   V_check_type VARCHAR(20);


    SELECT production_order_process_id,check_type INTO V_EXIST,V_check_type FROM production_order_process_log;

    IF V_EXIST=0 THEN

      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');

    ELSEIF V_check_type='stop_user' THEN


      /* UPDATE QUERY HERE */

    END IF;
    END $$

    DELIMITER ;

Unfortunately, stored procedures are going to have to wait until I can migrate this database into something my mySQL or msSQL like. Having my server tech trying to install the postgresql PDO packages earlier this week caused some pretty major problems. I know that they're included in PHP for mySQL. Though postgre has worked for me for a long while, I can't wait till I'm using mySQL! — 1252748, Jul 19 '12 at 11:08

score 1 · Answer 3 · answered Jul 19 '12 at 11:03

1

After insert type ON DUPLICATE KEY UPDATE...

answered Jul 19 '12 at 11:03

Viskovic

39
4

yes, but don't forget to give the column(s) that you want to be used as keys an INDEX or UNIQUE INDEX – Sliq Jul 19 '12 at 11:09

score 1 · Answer 4 · answered Jul 19 '12 at 11:03

Use MERGE statement

Here is the usage

MERGE INTO table [[AS] alias]
USING [table-ref | query]
ON join-condition
[WHEN MATCHED [AND condition] THEN MergeUpdate | DELETE]
[WHEN NOT MATCHED [AND condition] THEN MergeInsert]

MergeUpdate is
UPDATE SET { column = { expression | DEFAULT } |
          ( column [, ...] ) = ( { expression | DEFAULT } [, ...] ) }
[, ...]
(yes, there is no WHERE clause here)

MergeInsert is
INSERT [ ( column [, ...] ) ]
    { DEFAULT VALUES | VALUES ( { expression | DEFAULT } [, ...] )
[, ...]}
(no subquery allowed)

I'm sure you'll find more articles/examples if you search for it.

wildplasser · Answer 5 · 2012-07-19T11:19:38.217

Just ad a WHERE CLAUSE to the insert:

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')
WHERE NOT EXISTS ( SELECT *
        FROM production_order_process_log nx
        --
        --  assuming production_order_id is the Primary Key, here
        --
        WHERE nx.production_order_id = '$production_order_id'
        );

UPDATE: I was confused by the parameters and the VALUE() . The fragment below works without parameters, but with immediate values:

INSERT INTO tmp.production_order_process_log
           (  production_order_id, production_order_process_id, check_time, check_type)
    SELECT 1, 2, '2012-07-19 12:12:12', 'Lutser'
WHERE NOT EXISTS ( SELECT *
        FROM tmp.production_order_process_log nx
        --
        --  assuming production_order_id is the Primary Key, here
        --
        WHERE nx.production_order_id = 1
        );

(you'll have to change it a bit to re-add the parameters)

I apologize, but this is _just a bit_ over my head. production_order_id is the primary key for production order. what is nx? sorry if really basic confusion >< .. — 1252748, Jul 19 '12 at 11:11
The nx is the *correlation name* (aka as alias) for the subquery. The subquery tests if a row exists (for production_order_id=1) and returns True or False. And yes: it is really basic. And: this syntax *is* supported by postgres. (I tested it!) — wildplasser, Jul 19 '12 at 11:21
If you add the parameters again (sorry, I don't know PHP), dont forget to replace the literal '1' by $1; also in the subquery. — wildplasser, Jul 19 '12 at 11:30

UPDATE/INSERT based on if a row exists

5 Answers5

Linked