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

vertica_one_love

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.

Jim_Knicely

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

vertica_one_love

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_Knicely

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_love

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

Jim_Knicely

Cool!

By the way, I your user name

Deepak_Nailwal

I get the following error if i do the same -
ERROR [42601] ERROR: Subquery in MERGE is not supported;

Jim_Knicely

@Deepak_Nailwal - Can you post your SQL statement?

This works:

dbadmin=> SELECT version();
              version
------------------------------------
 Vertica Analytic Database v9.2.0-4
(1 row)

dbadmin=> SELECT * FROM my_merge;
 c1 | c2
----+----
  1 | A
(1 row)

dbadmin=> MERGE INTO my_merge m1 USING (SELECT 2 c1, 'B' c2) m2 ON (m1.c1 = m2.c1)
dbadmin->   WHEN MATCHED THEN UPDATE SET c2 = m2.c2
dbadmin->   WHEN NOT MATCHED THEN INSERT (c1, c2) VALUES (m2.c1, m2.c2);
 OUTPUT
--------
      1
(1 row)

dbadmin=> SELECT * FROM my_merge;
 c1 | c2
----+----
  1 | A
  2 | B
(2 rows)

See:
https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/AdministratorsGuide/Tables/MergeTables/MergeSourceData.htm

Deepak_Nailwal

I'm using Vertica 8.x. Probably it was introduced in Vertica 9.2.

Jim_Knicely

As a work around, create a database view using your subquery...

Example:

dbadmin=> SELECT * FROm my_merge;
 c1 | c2
----+----
  1 | A
(1 row)

dbadmin=> SELECT * FROM source;
 c1 | c2
----+----
  1 | B
  2 | C
  3 | D
  4 | E
(4 rows)

dbadmin=> CREATE VIEW source_temp_vw AS SELECT * FROM source WHERE c1 IN (1, 4);
CREATE VIEW

dbadmin=> SELECT * FROM source_temp_vw;
 c1 | c2
----+----
  1 | B
  4 | E
(2 rows)

dbadmin=> MERGE INTO my_merge m1 USING source_temp_vw m2 ON (m1.c1 = m2.c1)
dbadmin->  WHEN MATCHED THEN UPDATE SET c2 = m2.c2
dbadmin->  WHEN NOT MATCHED THEN INSERT (c1, c2) VALUES (m2.c1, m2.c2);
 OUTPUT
--------
      2
(1 row)

dbadmin=> SELECT * FROM my_merge;
 c1 | c2
----+----
  1 | B
  4 | E
(2 rows)