How to fix Setval: value out of bounds in PostgreSQL

PostgreSQLBEGINNERMEDIUMUpdated Dec 29, 2025· 155 views

The PostgreSQL "Setval: value out of bounds" error occurs when you try to set a sequence to a value that falls outside its defined minimum and maximum boundaries. This typically happens when setting a sequence to 0 (which violates the default MINVALUE of 1) or to a value below the sequence's custom MINVALUE. Fix it by checking the sequence's bounds and using appropriate values.

Quick fix

Fixes Setval: value out of bounds

-- Query the sequence metadata
SELECT sequencename, data_type, min_value, max_value, last_value, start_value
FROM pg_sequences
WHERE sequencename = 'your_sequence_name';
  1. Identify the sequence and its bounds
  2. Verify the value you're trying to set
  3. Use appropriate setval value (within bounds)
  4. Handle custom MINVALUE sequences
postgresql — error
-- Query the sequence metadata
SELECT sequencename, data_type, min_value, max_value, last_value, start_value
FROM pg_sequences
WHERE sequencename = 'your_sequence_name';
Setval: value out of bounds

What this error means

PostgreSQL sequences generate incrementing numbers for primary keys and auto-increment columns. Every sequence has defined bounds: a minimum value (MINVALUE) and maximum value (MAXVALUE). The setval() function is used to set the sequence's current value, but it strictly enforces these boundaries. If you attempt to set the sequence to a value outside these bounds, PostgreSQL throws the "Setval: value out of bounds" error. By default, sequences have MINVALUE of 1 (or -9223372036854775808 for descending sequences) and MAXVALUE of 9223372036854775807 for bigint. A common mistake is trying to set a sequence to 0, which violates the default MINVALUE. This can happen when resetting sequences after deleting all rows, or when migrating data and trying to reset auto-increment counters. Another scenario occurs when a sequence was created with an explicit custom MINVALUE that is higher than the value you're trying to set.

Typical symptoms

  • ERROR: setval: value X is out of bounds for sequence "sequence_name" (minvalue..maxvalue)
  • Sequence reset operations fail with bounds error
  • Migration scripts fail when trying to initialize sequences
  • Application initialization code fails when setting sequence starting values
  • Database restore procedures fail after bulk import

Common causes

  1. Attempting to set sequence to 0 when default MINVALUE is 1
  2. Setting a value below the sequence's custom MINVALUE
  3. Trying to set a value above the sequence's MAXVALUE
  4. Migration code not accounting for sequence bounds
  5. Resetting sequences to incorrect starting values in initialization scripts

How to fix "Setval: value out of bounds"

1Identify the sequence and its bounds

Check your PostgreSQL error logs to find the exact sequence name and the bounds mentioned in the error.

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

Note the MINVALUE and MAXVALUE from the output. By default, MINVALUE is 1 for ascending sequences.

2Verify the value you're trying to set

Check your application code or migration script to see what value is being passed to setval().

sql
-- 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

If your code is setting the value to 0, this is the problem.

3Use appropriate setval value (within bounds)

Ensure you're setting the sequence to a value within its defined range.

sql
-- 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

The third parameter to setval() (optional) controls whether the value should be marked as already used. false means the next nextval() will return this value; true means the next nextval() will return this value + 1.

4Handle custom MINVALUE sequences

If the sequence was created with a custom MINVALUE, respect that constraint:

sql
-- 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
5Use ALTER SEQUENCE as alternative

Instead of setval(), you can use ALTER SEQUENCE with RESTART to reset the sequence:

sql
-- 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

Note: ALTER SEQUENCE RESTART has different behavior than setval(). Choose based on your needs:
- setval(): Sets the current value; nextval() will return value+1
- ALTER SEQUENCE RESTART: Resets to start value; nextval() will return start value

6Handle zero/null edge cases in migration scripts

When migrating data or initializing sequences, use COALESCE to handle empty tables:

sql
-- 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']
);

This pattern ensures:
- Empty tables: sequence starts at 1 (0 + 1)
- Tables with data: sequence starts at MAX(id) + 1

7Prevent this error in application initialization

Update application code to respect sequence bounds:

typescript
// 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]
  );
}

Advanced notes

When designing sequences, consider:

1. Descending sequences: If you created a descending sequence (INCREMENT BY -1), its MINVALUE and MAXVALUE are reversed, and the starting value must be below the MINVALUE (which is now the upper bound).

sql
CREATE SEQUENCE countdown MAXVALUE 1 MINVALUE -100 INCREMENT BY -1 START 1;
   SELECT setval('countdown', -50);  -- Valid

2. CYCLE option: If a sequence reaches its MAXVALUE and has CYCLE enabled, it wraps back to MINVALUE rather than erroring:

sql
CREATE SEQUENCE cycling_ids CYCLE MINVALUE 1 MAXVALUE 100;
   -- When reaching 100, nextval() wraps to 1

Be careful with CYCLE on sequences used for primary keys, as it can create duplicate IDs.

3. Bulk operations: When performing bulk inserts followed by sequence reset, use a single transaction:

sql
BEGIN;
   DELETE FROM users;
   SELECT setval('user_id_seq', 1);
   COMMIT;

4. Monitoring sequence usage: Set up alerts for sequences approaching their MAXVALUE:

sql
SELECT sequencename, 
     ROUND((last_value::float / max_value::float) * 100, 2) as percent_used
   FROM pg_sequences
   WHERE last_value > (max_value * 0.8)
   ORDER BY percent_used DESC;

Frequently asked questions

What does "Setval: value out of bounds" mean?
PostgreSQL sequences generate incrementing numbers for primary keys and auto-increment columns. Every sequence has defined bounds: a minimum value (MINVALUE) and maximum value (MAXVALUE). The setval() function is used to set the sequence's current value, but it strictly enforces these boundaries.
How do I fix "Setval: value out of bounds"?
The PostgreSQL "Setval: value out of bounds" error occurs when you try to set a sequence to a value that falls outside its defined minimum and maximum boundaries. This typically happens when setting a sequence to 0 (which violates the default MINVALUE of 1) or to a value below the sequence's custom MINVALUE. Fix it by checking the sequence's bounds and using appropriate values.
What causes "Setval: value out of bounds"?
Attempting to set sequence to 0 when default MINVALUE is 1 Setting a value below the sequence's custom MINVALUE Trying to set a value above the sequence's MAXVALUE
Is the "Setval: value out of bounds" error serious?
It is a medium-severity error — more of a blocker than a risk. The steps above resolve it.

Official resources & further reading