Hi Alex,
thanks for the hint. I try the script via psql with (showduplicate.sql is your script):
Code Select
psql -h localhost -U netxms -p 5432 -a -f showduplicate.sqlBut the only output is the script himself, there are noch other outputs:
Code Select
xxx@xxx:~$ psql -h localhost -U netxms -p 5432 -a -f showduplicate.sql
Passwort für Benutzer netxms:
DO $$
DECLARE
node_record RECORD;
tbl_name TEXT;
dup_count INTEGER;
total_duplicates INTEGER := 0;
BEGIN
FOR node_record IN SELECT id FROM nodes
LOOP
tbl_name := 'idata_' || node_record.id;
IF EXISTS (
SELECT 1
FROM information_schema.tables
WHERE table_schema = 'public'
AND table_name = tbl_name
) THEN
EXECUTE format('
SELECT COUNT(*) FROM public.%I
WHERE ctid NOT IN (
SELECT MIN(ctid)
FROM public.%I
GROUP BY item_id, idata_timestamp
)', tbl_name, tbl_name) INTO dup_count;
IF dup_count > 0 THEN
RAISE NOTICE 'Table % has % duplicate rows', tbl_name, dup_count;
total_duplicates := total_duplicates + dup_count;
END IF;
END IF;
END LOOP;
RAISE NOTICE 'Total duplicate rows found: %', total_duplicates;
END $$;The script is running about 1h bevor i canceld it.
Am I on the right way to run the script?
Greetings
Marco
