CODEWITHSUNDEEP
copyuniquebulkinsertvertica
Kalpesh
Kalpesh

Reputation: 704

Insert Unique Records in Vertica with COPY query

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:)

Upvotes: 2

Views: 1558

Answers (2)

Kermit
Kermit

Reputation: 34054

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.

Upvotes: 4

Chris McFadden
Chris McFadden

Reputation: 41

Another way to do this, especially if you are trying to do UPSERTS, is to COPY into a temporary table and then use the MERGE statement. This will merge the data from the temp table into your table without risk of duplicates. It is very performant when loading batches of data. If you look at the docs on temporary tables and MERGE you should be able to understand how to use them.

Upvotes: 4

Related Questions