Conditional if else insert or update SQL-CodePudding

I have a batch insert where I would need to check for some columns conditionally and do either insert or update. Right now I am doing this by checking a conflict on one column:

const val sql = """
              INSERT INTO customer (name, customer_code, org_number, address, zip_code, zip_area, country_code, created_by, updated_by, subcustomer, credit_blocked, uuid, is_deleted)
              values (...values)
                on conflict (uuid) do update
              set

I have records in the customer table that don't have the value for the uuid field. In that case I should check if the name and subcustomer match and do the update. So in pseudo code something like this:

IF (uuid == values.uuid)
   do updated
ELSE IF (name == values.name AND subcustomer == values.subcustomer)
   do updated
ELSE do insert

I am not sure how to do that with batch inserts?

CodePudding user response：

I don't see how to do everything within an INSERT ... ON CONFLICT DO UPDATE ... statement. You can keep your initial statement to test the uuid value when it is not NULL :

INSERT INTO customer (name, customer_code, org_number, address, zip_code, zip_area, country_code, created_by, updated_by, subcustomer, credit_blocked, uuid, is_deleted)
              values (...values)
                on conflict (uuid) do update
              set ...

And then you can either create a trigger function or a rule :

Trigger function :

CREATE OR REPLACE FUNCTION before_insert_customer ()
RETURNS trigger LANGUAGE plpgsql IMMUTABLE AS
$$
BEGIN
IF EXISTS
  ( SELECT 1
      FROM customer
     WHERE uuid IS NULL
       AND name = NEW.name
       AND subcustomer = NEW.subcustomer
  )
THEN
  UPDATE customer
     SET ...
   WHERE uuid IS NULL
     AND name = NEW.name 
     AND subcustomer = NEW.subcustomer ;
  RETURN NULL ;
ELSE
  RETURN NEW ;
END IF ;
END ;
$$

CREATE OR REPLACE TRIGGER before_insert_customer 
BEFORE INSERT ON customer 
FOR EACH ROW EXECUTE FUNCTION before_insert_customer () ;

Rule :

CREATE OR REPLACE RULE test_insert AS
    ON INSERT TO customer
 WHERE EXISTS
       ( SELECT 1
           FROM customer
          WHERE uuid IS NULL
            AND name = NEW.name 
            AND subcustomer = NEW.subcustomer
       )
    DO INSTEAD
       UPDATE customer
          SET ...
        WHERE uuid IS NULL
          AND name = NEW.name 
          AND subcustomer = NEW.subcustomer ;