How to generate GROUP BY script based on priamry key in Netezza

Question

In Netezza, primary key is not enforced. Thread How to overcome Netezza's lack of unique constraint/referential integrity enforcement? answers how to write to stored procedure to enforce it.

In our case, we don't want to enforce it using a stored procedure because it will break our loading. Instead, we check our database integrity monthly to find potential duplicates. We need to a script to help us generate the following query for all tables:

SELECT {PK1}, {PK2}, COUNT(*)
FROM {TABLENAME}
GROUP BY {PK1}, {PK2} HAVING COUNT(*)>1
LIMIT 100;

Possible duplicate of [How to overcome Netezza's lack of unique constraint/referential integrity enforcement?](http://stackoverflow.com/questions/5649297/how-to-overcome-netezzas-lack-of-unique-constraint-referential-integrity-enforc) — Jeremy Fortune, May 02 '16 at 20:18
No. It is not a duplicates. The one you mention is to use stored procedure to enforce primary key. My question is for a query generator. — Hongfa Wu, May 04 '16 at 15:46
Perhaps, but the answer is exactly the same. In order to enforce a primary key, you have to find the duplicates, which is exactly what the answer to that question does. — Jeremy Fortune, May 04 '16 at 18:35

score 0 · Answer 1 · edited May 23 '17 at 12:34

The following answer use knowledge of my another post https://stackoverflow.com/a/36989929/4266330

The following script will return the result you want

SELECT 'SELECT '||GROUP_BY_COLS||', COUNT(*)'||CHR(13)||
       '  FROM '||SCHEMA||'.'||TABLENAME||CHR(13)||
       ' GROUP BY '||GROUP_BY_COLS||' HAVING COUNT(*)>1'||CHR(13)||
       ' LIMIT 100;'
  FROM (       
SELECT 
     R.SCHEMA  AS SCHEMA
    ,R.NAME    AS TABLENAME
    ,TOOLKIT.SQLEXT.REGEXP_REPLACE(TOOLKIT.SQLEXT.GROUP_CONCAT('@'||LPAD(K.CONSEQ, 4, '0')||'@'||K.ATTNAME, ', '),
                                   '@[0-9]{4}@', '') AS GROUP_BY_COLS
  FROM _V_RELATION_COLUMN R
  JOIN _V_RELATION_KEYDATA K
    ON (R.SCHEMA=K.SCHEMA
   AND R.NAME=K.RELATION
   AND R.ATTNAME=K.ATTNAME)
 WHERE K.CONTYPE='p'
 GROUP BY 1, 2 
 ) S;

Sample result:


    SELECT IDP_EFFECTIVE_DATE, ACCOUNT_ID, COUNT(*)
      FROM AAA.L1_ACCOUNTS
     GROUP BY IDP_EFFECTIVE_DATE, ACCOUNT_ID HAVING COUNT(*)>1
     LIMIT 100;
    SELECT DEAL_NUM, COUNT(*)
      FROM ABUHARI.L1_BMO16_BOOST_EXTRACT_TOR
     GROUP BY DEAL_NUM HAVING COUNT(*)>1
     LIMIT 100;
    SELECT IDP_WAREHOUSE_ID, IDP_AUDIT_ID, ACCOUNT_KEY, COUNT(*)
      FROM ACTIMIZE.L1_ACTIMIZE_US12_ACCOUNT
     GROUP BY IDP_WAREHOUSE_ID, IDP_AUDIT_ID, ACCOUNT_KEY HAVING COUNT(*)>1
     LIMIT 100;

How to generate GROUP BY script based on priamry key in Netezza

1 Answers1