SQL MAXVALUE

What is SQL MAXVALUE used for?

MAXVALUE sets the highest allowed value for a sequence or marks an open-ended upper bound in range partitioning.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL MAXVALUE: PostgreSQL, Oracle, MariaDB, IBM Db2, SQL Server (sequence only), Amazon Redshift (sequence only). Not supported by MySQL prior to 8.0 and SQLite.

SQL MAXVALUE Full Explanation

MAXVALUE is a reserved keyword that serves two major purposes:1. Sequence generator limit – In CREATE SEQUENCE or ALTER SEQUENCE statements, MAXVALUE specifies the greatest value the sequence can return. Once reached, the next value either raises an error (default) or cycles back to MINVALUE if CYCLE is enabled. If MAXVALUE is omitted, the database assigns a very large default (the maximum of the data type).2. Range-partition bound – When defining range partitions, MAXVALUE acts as a sentinel meaning "infinity." It is supplied in the TO (upper) bound so that all values above the preceding partition’s upper limit fall into the partition that ends with MAXVALUE. Only one partition per partition key can use MAXVALUE, and it must be the last in order.Behavior notes:- MAXVALUE applies per sequence; different sequences can have different limits.- Changing MAXVALUE with ALTER SEQUENCE affects only future NEXTVAL operations.- For partitions, MAXVALUE cannot be combined with explicit expressions in the same bound list element.- Not all dialects allow MAXVALUE outside of sequence or partition statements.- Using MAXVALUE without CYCLE on a frequently used sequence risks “sequence exhausted” errors once the limit is hit.

SQL MAXVALUE Syntax

-- Sequence definition
CREATE SEQUENCE seq_name
  START WITH 1
  INCREMENT BY 1
  MAXVALUE 1000000;

ALTER SEQUENCE seq_name MAXVALUE 5000000;

-- Range-partition definition (PostgreSQL style)
CREATE TABLE orders (
  order_id   bigint,
  order_date date
) PARTITION BY RANGE (order_date);

CREATE TABLE orders_past PARTITION OF orders
  FOR VALUES FROM ('2000-01-01') TO ('2020-01-01');

CREATE TABLE orders_current PARTITION OF orders
  FOR VALUES FROM ('2020-01-01') TO (MAXVALUE);

SQL MAXVALUE Parameters

value (numeric) - The highest integer the sequence can generate or, in partitioning, the literal keyword MAXVALUE indicating an unbounded upper limit.

Example Queries Using SQL MAXVALUE

-- 1. Create a bounded sequence
CREATE SEQUENCE invoice_seq
  START WITH 1
  INCREMENT BY 1
  MAXVALUE 999999;

-- 2. Expand the limit later
ALTER SEQUENCE invoice_seq MAXVALUE 99999999;

-- 3. Define a catch-all range partition
CREATE TABLE traffic (
  id        bigint,
  visit_day date
) PARTITION BY RANGE (visit_day);

CREATE TABLE traffic_archive PARTITION OF traffic
  FOR VALUES FROM (MINVALUE) TO ('2024-01-01');

CREATE TABLE traffic_live PARTITION OF traffic
  FOR VALUES FROM ('2024-01-01') TO (MAXVALUE);

Expected Output Using SQL MAXVALUE

Sequence created; NEXTVAL will never exceed 999999 until altered.
Sequence altered; subsequent NEXTVAL calls can progress up to 99,999,999.
Rows with visit_day on or after 2024-01-01 are routed to the traffic_live partition.

Use Cases with SQL MAXVALUE

Cap a sequence to prevent key collisions with business identifiers.
Reserve ID space above a threshold for future use.
Build time-based range partitions where the last partition should accept all future dates.
Enforce data retention by splitting historic data into a bounded partition and new data into an unbounded one.

Common Mistakes with SQL MAXVALUE

Forgetting to raise MAXVALUE before a high-volume sequence reaches its limit.
Assuming MAXVALUE implies CYCLE – it does not.
Mixing explicit values and MAXVALUE within the same partition bound list.
Using MAXVALUE in databases that do not support it for the chosen feature.

First Introduced In

Oracle Database 7 (1992) for sequences

Frequently Asked Questions

What is the default MAXVALUE for a sequence?

If you omit MAXVALUE, the database sets it to the largest value allowed by the sequence’s data type (e.g., 9223372036854775807 for bigint in PostgreSQL).

Does MAXVALUE automatically enable CYCLE?

No. CYCLE must be specified explicitly. Without CYCLE, reaching MAXVALUE raises an error.

Can I set MAXVALUE lower than the current sequence value?

No. The new MAXVALUE must be greater than or equal to the current sequence last_value, or ALTER SEQUENCE will fail.

Is MAXVALUE case-sensitive?

Keyword spelling is not case-sensitive, but values supplied for sequences are numeric and must follow dialect-specific numeric literal rules.