Insert Unique Records in Vertica with COPY query

Question

I am new to Vertica DB, I worked with Mysql previously. I wanted to insert unique records in vertica table, but vertica doesn't support unique constraints while insertion. I am inserting records in table by COPY query. So I can't check each records before insertion, is exist or not. Can some one help me with optimized way for unique insertion.

Thanks in advance:)

Answer 1

You can add NO COMMIT to your COPY and run ANALYZE_CONSTRAINTS before a COMMIT:

dbadmin=> CREATE TABLE tbl (a int PRIMARY KEY);
CREATE TABLE
dbadmin=> COPY tbl FROM STDIN NO COMMIT;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> 1
>> 2
>> 2
>> 3
>> \.
dbadmin=> SELECT * FROM tbl;
 a
---
 1
 2
 2
 3
(4 rows)

dbadmin=> SELECT ANALYZE_CONSTRAINTS('tbl');
 Schema Name | Table Name | Column Names | Constraint Name | Constraint Type | Column Values
-------------+------------+--------------+-----------------+-----------------+---------------
 public      | tbl        | a            | C_PRIMARY       | PRIMARY         | ('2')
(1 row)

dbadmin=> DELETE FROM tbl WHERE a = 2;
 OUTPUT
--------
      2
(1 row)

dbadmin=> COMMIT;
COMMIT
dbadmin=> SELECT * FROM tbl;
 a
---
 1
 3
(2 rows)

This is obviously a simplistic example.

I've covered this topic on my blog post, Enforcing Uniqueness of Data on Load.

Update: as of 7.2, Vertica can automatically enforce PRIMARY and UNIQUE constraints on load.