sql - Vertica:重复/主键的数据验证

我正在尝试在加载期间创建一个验证过程，该过程将检查以确保数据不重复。 Vertica native 不支持此功能:



问题是我无法弄清楚如何以编程方式执行此操作。我怀疑我需要一个存储过程，但是我对vertica的存储过程语法/限制不熟悉。你能帮我吗？这是我所拥有的:

-- Create a new table. "id" is auto-incremented and "name" must be unique
CREATE TABLE IF NOT EXISTS my_table (
id IDENTITY
, name varchar(50) UNIQUE NOT NULL
, type varchar(20)
, description varchar(200)
);

--Insert a record
begin;
copy my_table from stdin
abort on error
NO COMMIT; -- this begins the load
name1|type1|description1 --this is the load
\. -- this closes the load
commit;

-- insert the duplicate record
begin;
copy my_table from stdin
abort on error
NO COMMIT; -- this begins the load
name1|type1|description1 --this is the load
\. -- this closes the load
commit; -- Surprisingly, the load executes successfully! What's going on?!?!

-- Check constraints. We see that there is a failed constraints:
select analyze_constraints('my_table');

我的想法是做一些条件逻辑。伪代码如下。您能帮我为Vertica做准备吗？

Begin
load data
if (select count(*) from (select analyze_constraints('my_table')) sub) == 0:
commit
else rollback

最佳答案

-- Start by Setting Vertica up to rollback and return an error code
-- if an error is encountered.

\set ON_ERROR_STOP on

-- Load Data here (code omitted since you already have this)


-- Raise an Error condition by selecting 1/0 if any rows were rejected
-- during the load
SELECT
         GET_NUM_REJECTED_ROWS() AS NumRejectedRows
        ,GET_NUM_ACCEPTED_ROWS() AS NumAcceptedRows
;

SELECT 1 / (1-SIGN(GET_NUM_REJECTED_ROWS()));


-- Raise an Error condition if there are duplicates in my_table
SELECT 1 / ( 1 - SIGN( COUNT(*) ) )
FROM ( SELECT  name1,type1,description1
         FROM MY_TABLE
       GROUP BY 1,2,3
       HAVING COUNT(*) > 1 ) AS T1 ;

-- Raise an Error if primary key constraint is violated.
SELECT 1 / ( 1 - SIGN( COUNT(*) ) )
FROM (SELECT  ANALYZE_CONSTRAINTS ('my_table')) AS T1;

COMMIT;

关于sql - Vertica:重复/主键的数据验证，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/12648470/