MySQL INSERT ... ON DUPLICATE KEY UPDATE - need vertica analogue

In MySQL if you trying to insert a record, and it violates unique/primary constraint, it can update existing record.
https://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html

Does vertica have anything similar?

Thanks.

Comments

  • Jim_KnicelyJim_Knicely Employee, Registered User, VerticaExpert
    edited October 4

    No you can't do that in Vertica. Why? That functionality is the result of lazy development.

  • vertica_one_lovevertica_one_love Registered User
    edited October 5

    So, what is 'not lazy' option?
    I need to save large amount of data to db - fast.
    In Mysql I can group updates to batches, and perform batch in single transatction. And I don't need to check if records in batch violates constraints.

    Any ideas how can I achieve this in vertica would be helpful.
    Thanks.

  • Jim_KnicelyJim_Knicely Employee, Registered User, VerticaExpert

    Primary keys can be enabled or disabled. By default they are disabled. You can change that behavior via the EnableNewPrimaryKeysByDefault database parameter.

    dbadmin=> select current_value, default_value, description from configuration_parameters where parameter_name = 'EnableNewPrimaryKeysByDefault';
     current_value | default_value |                                description
    ---------------+---------------+---------------------------------------------------------------------------
     0             | 0             | Determines whether new primary key constraints will be enabled by default
    (1 row)
    

    Note you can always UPDATE a PK even when a PK is enabled.

    Example:

    dbadmin=> create table update_pk (c1 int not null, c2 varchar(10));
    CREATE TABLE
    
    dbadmin=> alter table update_pk add constraint update_pk_pk primary key (c1);
    ALTER TABLE
    
    dbadmin=> select is_enabled from primary_keys where constraint_name = 'update_pk_pk';
     is_enabled
    ------------
     f
    (1 row)
    
    dbadmin=> insert into update_pk select 1, 'OLD VALUE';
     OUTPUT
    --------
          1
    (1 row)
    
    dbadmin=> update update_pk set c1 = 2, c2 = 'NEW VALUE';
     OUTPUT
    --------
          1
    (1 row)
    
    dbadmin=> select * from update_pk;
     c1 |    c2
    ----+-----------
      2 | NEW VALUE
    (1 row)
    
    dbadmin=> alter table update_pk alter constraint update_pk_pk enabled;
    ALTER TABLE
    
    dbadmin=> update update_pk set c1 = 1, c2 = 'OLD VALUE';
     OUTPUT
    --------
          1
    (1 row)
    
    dbadmin=> select * from update_pk;
     c1 |    c2
    ----+-----------
      1 | OLD VALUE
    (1 row)
    
    dbadmin=> insert into update_pk select 1, 'NEW VALUE';
    ERROR 6745:  Duplicate key values: 'c1=1' -- violates constraint 'public.update_pk.update_pk_pk'
    
    dbadmin=> alter table update_pk alter constraint update_pk_pk disabled;
    ALTER TABLE
    dbadmin=> insert into update_pk select 1, 'NEW VALUE';
     OUTPUT
    --------
          1
    (1 row)
    
    dbadmin=> select * from update_pk;
     c1 |    c2
    ----+-----------
      1 | OLD VALUE
      1 | NEW VALUE
    (2 rows)
    

    Maybe the MERGE command can do what you want?

    Here I UPDATE the matching rows:

    dbadmin=> merge into update_pk using (select 1 c1, 'HMM' c2) foo on foo.c1 = update_pk.c1
    dbadmin-> when matched then update set c1 = foo.c1, c2 = foo.c2
    dbadmin-> when not matched then insert (c1, c2) values (foo.c1, foo.c2);
     OUTPUT
    --------
          2
    (1 row)
    
    dbadmin=> select * from update_pk;
     c1 | c2
    ----+-----
      1 | HMM
      1 | HMM
    (2 rows)
    

    And here I INSERT a new row as there are no matching records:

    dbadmin=> merge into update_pk using (select 2 c1, 'NEW_VALUE' c2) foo on foo.c1 = update_pk.c1
    dbadmin-> when matched then update set c1 = foo.c1, c2 = foo.c2
    dbadmin-> when not matched then insert (c1, c2) values (foo.c1, foo.c2);
     OUTPUT
    --------
          1
    (1 row)
    
    dbadmin=> select * from update_pk;
     c1 |    c2
    ----+-----------
      1 | HMM
      1 | HMM
      2 | NEW_VALUE
    (3 rows)
    
  • vertica_one_lovevertica_one_love Registered User

    Yes, That's what I was looking for!
    Many-many thanks.

  • Jim_KnicelyJim_Knicely Employee, Registered User, VerticaExpert
    edited October 5

    Cool!

    By the way, I <3 your user name :p