How to fix Setval: value out of bounds in PostgreSQL

-- Query the sequence metadata
SELECT sequencename, data_type, min_value, max_value, last_value, start_value
FROM pg_sequences
WHERE sequencename = 'your_sequence_name';

-- This will fail if trying to set to 0 or negative number
SELECT setval('user_id_seq', 0);  -- ERROR: value 0 is out of bounds

-- Correct: set to 1 or higher (within MINVALUE..MAXVALUE)
SELECT setval('user_id_seq', 1);  -- SUCCESS

-- Set to 1 (the default minimum and typical starting value)
SELECT setval('user_id_seq', 1);

-- Or set based on the maximum existing ID in the table:
SELECT setval('user_id_seq', COALESCE((SELECT MAX(id) FROM users), 0) + 1);

-- If the table is empty and you want to restart from 1:
SELECT setval('user_id_seq', 1, false);  -- false means the value hasn't been used yet

-- Example: sequence with custom MINVALUE of 100
CREATE SEQUENCE product_id_seq MINVALUE 100 START 100;

-- This will fail:
SELECT setval('product_id_seq', 50);  -- ERROR: 50 is below MINVALUE 100

-- This will succeed:
SELECT setval('product_id_seq', 100);  -- SUCCESS

-- Check if you need to change the MINVALUE:
ALTER SEQUENCE product_id_seq MINVALUE 1;  -- Then setval to 1 is allowed

-- Restart sequence from 1
ALTER SEQUENCE user_id_seq RESTART WITH 1;

-- This is equivalent to setval() but with different semantics:
-- ALTER SEQUENCE doesn't increment after the restart value
-- It's useful when you want to reset the sequence completely

-- Safe pattern: handles both empty tables and existing data
SELECT setval(
  'user_id_seq',
  COALESCE((SELECT MAX(id) FROM users), 0) + 1
);

-- In application code (e.g., Node.js with node-postgres):
await client.query(
  `SELECT setval($1, COALESCE((SELECT MAX(id) FROM users), 0) + 1)`,
  ['user_id_seq']
);

// TypeScript/Node.js example
async function resetSequence(sequenceName: string, tableName: string, idColumn: string) {
  // First, get the sequence bounds
  const boundsResult = await client.query(
    `SELECT min_value, max_value FROM pg_sequences WHERE sequencename = $1`,
    [sequenceName]
  );
  
  if (boundsResult.rows.length === 0) {
    throw new Error(`Sequence ${sequenceName} not found`);
  }

  const { min_value } = boundsResult.rows[0];
  
  // Get the maximum ID in the table (or use min_value if empty)
  const maxIdResult = await client.query(
    `SELECT COALESCE(MAX(${idColumn}), $1 - 1) as next_value FROM ${tableName}`,
    [min_value]
  );
  
  const nextValue = maxIdResult.rows[0].next_value + 1;
  
  // Now setval with a value we know is valid
  await client.query(
    `SELECT setval($1, $2)`,
    [sequenceName, nextValue]
  );
}