How to fix 21000: cardinality_violation in PostgreSQL

-- Example error context from logs
ERROR: more than one row returned by a subquery used as an expression
Context: UPDATE summary SET staff_id = (SELECT COUNT(staff_id) FROM details GROUP BY staff_id)

-- For example, if your problematic query is:
-- UPDATE summary SET staff_id = (SELECT COUNT(staff_id) FROM details GROUP BY staff_id);

-- Test the subquery in isolation:
SELECT COUNT(staff_id) FROM details GROUP BY staff_id;

-- If this returns multiple rows, you've found the problem

-- Original (broken):
UPDATE summary SET staff_id = (SELECT COUNT(staff_id) FROM details GROUP BY staff_id);

-- Fixed:
UPDATE summary SET staff_id = (SELECT COUNT(staff_id) FROM details GROUP BY staff_id LIMIT 1);

-- If you want the maximum count:
UPDATE summary SET staff_id = (SELECT MAX(count) FROM (SELECT COUNT(staff_id) as count FROM details GROUP BY staff_id) sub);

-- Or sum all counts:
UPDATE summary SET staff_id = (SELECT SUM(cnt) FROM (SELECT COUNT(*) as cnt FROM details GROUP BY staff_id) sub);

UPDATE summary SET staff_id = (SELECT id FROM employees WHERE department = 'sales' AND status = 'active' LIMIT 1);

UPDATE summary SET staff_id = (SELECT id FROM employees ORDER BY hire_date DESC LIMIT 1);

-- Problematic query with duplicate values:
INSERT INTO users (id, name) VALUES (1, 'Alice'), (1, 'Alice'), (2, 'Bob')
ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name;
-- ERROR: cardinality_violation

-- Solution: Remove duplicates from input before inserting
INSERT INTO users (id, name) VALUES (1, 'Alice'), (2, 'Bob')
ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name;

// TypeScript/Node.js example
const records = [
  { id: 1, name: 'Alice' },
  { id: 1, name: 'Alice' },  // duplicate
  { id: 2, name: 'Bob' },
];

// Deduplicate by primary key
const uniqueRecords = Array.from(
  new Map(records.map(r => [r.id, r])).values()
);

// Now safely use in ON CONFLICT
await db.users.createMany({ data: uniqueRecords, skipDuplicates: true });

-- Test with sample data
BEGIN;
  -- Your modified query here
  UPDATE summary SET staff_id = (SELECT COUNT(*) FROM details);
  
  -- Check the results
  SELECT * FROM summary LIMIT 5;
ROLLBACK;  -- Undo changes to test again