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

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

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.

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)

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

Cool!

By the way, I your user name

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

@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

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

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)