CREATE_SEQUENCE(7pg16)

💫 CREATE SEQUENCE 💫

okay bestie, so this is the PostgreSQL 16 page for CREATE SEQUENCE (manual page CREATE_SEQUENCE(7)) and literally it's the glow-up your auto-incrementing numbers deserve ✨

---

🌸 SYNOPSIS 🌸

CREATE [ { TEMPORARY | TEMP } | UNLOGGED ] SEQUENCE [ IF NOT EXISTS ] name
    [ AS data_type ]
    [ INCREMENT [ BY ] increment ]
    [ MINVALUE minvalue | NO MINVALUE ] [ MAXVALUE maxvalue | NO MAXVALUE ]
    [ START [ WITH ] start ] [ CACHE cache ] [ [ NO ] CYCLE ]
    [ OWNED BY { table_name.column_name | NONE } ]

---

🦋 NAME 🦋

CREATE_SEQUENCE — define a new sequence generator 👑

---

🎀 DESCRIPTION 🎀

So CREATE SEQUENCE makes a brand-new sequence number generator. Under the hood? It's creating and initializing a whole new special single-row table with the name name, and the generator is owned by whoever ran the command 🫶

If you hand it a schema name, the sequence gets created in that schema. If you don't, it lands in the current schema. Temporary sequences live in their own special schema, so ngl you literally cannot give a schema name when you're making a temp sequence 🙈 Also, the sequence name has to be distinct from the name of any other relation in the same schema — that's tables, sequences, indexes, views, materialized views, and foreign tables. all of them. no sharing 💅

Once it exists, you operate on it with nextval, currval, and setval — those live over in Section 9.17, go say hi 💖

You can't update a sequence directly, but you can peek at its parameters and current state with a query like:

SELECT * FROM name;

last_value shows the last value allocated by any session. ok but real talk — that value might already be stale by the time it prints, if other sessions are out here actively doing nextval calls 😭 that's just how it is.

---

✨ PARAMETERS ✨

• TEMPORARY or TEMP — makes the sequence object exist only for this session, and it gets auto-dropped when the session exits. While the temp sequence exists, existing permanent sequences with the same name are not visible in this session — unless you reference them with schema-qualified names 👀
• UNLOGGED — creates it as an unlogged sequence. Changes to unlogged sequences are not written to the write-ahead log, and they are not crash-safe: an unlogged sequence automatically resets to its initial state after a crash or unclean shutdown 💀 Unlogged sequences also aren't replicated to standby servers.

unlike unlogged tables, unlogged sequences don't give you a significant performance advantage. The real reason this option exists is for sequences tied to unlogged tables via identity columns or serial columns — in those cases it usually wouldn't make sense for the sequence to be WAL-logged and replicated while its table isn't 🤷‍♀️

• IF NOT EXISTS — don't throw an error if a relation with that name already exists; you just get a notice instead. but bestie, note that there is no guarantee the existing relation is anything like the sequence you would have created — it might not even be a sequence 😳
• name — the name (optionally schema-qualified) of the sequence to be created 🏷️
• data_type — the optional AS data_type clause sets the data type of the sequence. Valid types are smallint, integer, and bigint, with bigint as the default. The type determines the default minimum and maximum values of the sequence 🔢
• increment — the optional INCREMENT BY increment clause says which value gets added to the current sequence value to make a new one. Positive value → ascending sequence, negative value → descending sequence. Default is 1 ⬆️⬇️
• minvalue / NO MINVALUE — the optional MINVALUE minvalue clause sets the minimum value the sequence can generate. If you don't supply it, or you say NO MINVALUE, defaults kick in: for an ascending sequence the default is 1, and for a descending sequence it's the minimum value of the data type 🌱
• maxvalue / NO MAXVALUE — the optional MAXVALUE maxvalue clause sets the maximum value for the sequence. Skip it or say NO MAXVALUE and defaults apply: for an ascending sequence the default is the maximum value of the data type, and for a descending sequence it's -1 🔝
• start — the optional START WITH start clause lets the sequence begin anywhere it wants, main character energy. Default starting value is minvalue for ascending sequences and maxvalue for descending ones 🚀
• cache — the optional CACHE cache clause says how many sequence numbers get preallocated and stored in memory for faster access. Minimum value is 1 (only one value at a time, i.e. no cache), and 1 is also the default ⚡
• CYCLE / NO CYCLE — CYCLE lets the sequence wrap around once it hits maxvalue (ascending) or minvalue (descending). When the limit's reached, the next number generated is minvalue or maxvalue respectively 🔄

With NO CYCLE, any calls to nextval after the sequence has hit its maximum value return an error ⛔ If neither CYCLE nor NO CYCLE is specified, NO CYCLE is the default.

• OWNED BY table_name.column_name / OWNED BY NONE — OWNED BY associates the sequence with a specific table column, so if that column (or its whole table) gets dropped, the sequence gets automatically dropped too 🪄 The specified table must have the same owner and be in the same schema as the sequence. OWNED BY NONE is the default and means there's no such association.

---

💜 NOTES 💜

Use DROP SEQUENCE to remove a sequence 🗑️

Sequences are based on bigint arithmetic, so the range can't exceed the range of an eight-byte integer: -9223372036854775808 to 9223372036854775807 🔥

Because nextval and setval calls are never rolled back, sequence objects can't be used if you need "gapless" assignment of sequence numbers 😭 You can build gapless assignment using exclusive locking of a table containing a counter, but that solution is wayyyyy more expensive than sequence objects — especially when lots of transactions need sequence numbers concurrently.

⚠️ unexpected results might be obtained if a cache setting greater than one is used for a sequence object that'll be used concurrently by multiple sessions. Each session allocates and caches successive sequence values during one access to the sequence object and bumps the object's last_value accordingly. Then the next cache-1 uses of nextval within that session just return the preallocated values without touching the sequence object. So any numbers allocated but not used within a session are lost when that session ends → "holes" in the sequence 🕳️

Furthermore, even though multiple sessions are guaranteed to allocate distinct sequence values, the values might come out out of sequence when you look at all the sessions together. Example time: with a cache setting of 10, session A might reserve values 1..10 and return nextval=1, then session B might reserve values 11..20 and return nextval=11 before session A has even generated nextval=2 🙈 So with a cache setting of one it's safe to assume nextval values are generated sequentially; with a cache setting greater than one you should only assume that the nextval values are all distinct, not that they're generated purely sequentially. Also, last_value reflects the latest value reserved by any session, whether or not it's actually been returned by nextval yet.

Another consideration: a setval executed on such a sequence won't be noticed by other sessions until they've used up any preallocated values they've cached 👀

---

🌈 EXAMPLES 🌈

Create an ascending sequence called serial, starting at 101:

CREATE SEQUENCE serial START 101;

Select the next number from this sequence:

SELECT nextval('serial');

 nextval
---------
     101

Select the next number from this sequence:

SELECT nextval('serial');

 nextval
---------
     102

Use this sequence in an INSERT command:

INSERT INTO distributors VALUES (nextval('serial'), 'nothing');

Update the sequence value after a COPY FROM:

BEGIN;
COPY distributors FROM 'input_file';
SELECT setval('serial', max(id)) FROM distributors;
END;

---

🧚 COMPATIBILITY 🧚

CREATE SEQUENCE conforms to the SQL standard, with these exceptions, bestie:

• Obtaining the next value is done using the nextval() function instead of the standard's NEXT VALUE FOR expression.
• The OWNED BY clause is a PostgreSQL extension.

---

👑 SEE ALSO 👑

ALTER SEQUENCE (ALTER_SEQUENCE(7)), DROP SEQUENCE (DROP_SEQUENCE(7))

---

ok that's the whole page, mwah 💋 — brought to you by PostgreSQL 16.15, dated 2026, filed under CREATE SEQUENCE(7). go forth and generate numbers, icon 🫶✨