PostgreSQL allows us to create sequence objects, otherwise known as “sequence generators”, or simply “sequences”. As the name suggests, a sequence object is used to generate sequence numbers. <\/p>\n\n\n\n
We can use sequences to generate incrementing numbers or decrementing numbers.<\/p>\n\n\n\n\n\n\n\n
The syntax for creating a sequence generator goes like this:<\/p>\n\n\n\n
CREATE [ { TEMPORARY | TEMP } | UNLOGGED ] SEQUENCE [ IF NOT EXISTS ] name\n [ AS data_type ]\n [ INCREMENT [ BY ] increment ]\n [ MINVALUE minvalue | NO MINVALUE ] [ MAXVALUE maxvalue | NO MAXVALUE ]\n [ START [ WITH ] start ] [ CACHE cache ] [ [ NO ] CYCLE ]\n [ OWNED BY { table_name.column_name | NONE } ]<\/code><\/pre>\n\n\n\nExample<\/h2>\n\n\n\n
Here’s an example of creating a sequence in PostgreSQL:<\/p>\n\n\n\n
CREATE SEQUENCE Sequence1;<\/code><\/pre>\n\n\n\nThis is all that’s required to create a sequence object. We specify CREATE SEQUENCE<\/code>, followed by the name we want to give to the sequence.<\/p>\n\n\n\nThat creates a sequence with the default options. <\/p>\n\n\n\n
Specify Options for the Sequence<\/h2>\n\n\n\n
We can also specify our own custom options for the sequence. <\/p>\n\n\n\n
Here’s an example:<\/p>\n\n\n\n
CREATE SEQUENCE Sequence2\n AS integer\n INCREMENT 10\n MINVALUE 100\n MAXVALUE 250000\n START 101\n CACHE 1\n NO CYCLE;<\/code><\/pre>\n\n\n\nHere’s what those sequence options do:<\/p>\n\n\n\nINCREMENT<\/code><\/td>Specifies which value is added to the current sequence value to create a new value. A positive value makes it an ascending sequence, a negative one a descending sequence. The default value is 1<\/code>.<\/td><\/tr>MINVALUE<\/code><\/td>Specifies the minimum value a sequence can generate. If this clause is not supplied or NO MINVALUE<\/code> is specified, then default values are used. The default for an ascending sequence is 1<\/code>. The default for a descending sequence is the minimum value of the data type.<\/td><\/tr>MAXVALUE<\/code><\/td>Specifies the maximum value for the sequence. If this clause is not supplied or NO MAXVALUE<\/code> is specified, then default values are used. The default for an ascending sequence is the maximum value of the data type. The default for a descending sequence is -1<\/code>.<\/td><\/tr>START<\/code><\/td>Specifies the value to start the sequence at. The default value for ascending sequences is the MINVALUE<\/code> amount, and the default for descending sequences is the MAXVALUE<\/code> amount.<\/td><\/tr>CACHE<\/code><\/td>Specifies how many sequence numbers are to be preallocated and stored in memory for faster access. The default value is 1<\/code>, which is also the minimum value.<\/td><\/tr>CYCLE<\/code>
NO CYCLE<\/code><\/td>We can use CYCLE<\/code> or NO CYCLE<\/code> to specify whether or not the sequence will start again when it reaches its MINVALUE<\/code> or MAXVALUE<\/code> amount (depending on whether it’s an ascending sequence or descending one). If NO CYCLE<\/code> is used, an error will occur once the sequence tries to generate a value that exceeds its MINVALUE<\/code>\/MAXVALUE<\/code>.<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\nData Type<\/h2>\n\n\n\n
In the previous example I specified the data type as integer<\/code>. We have the option of smallint<\/code>, integer<\/code>, and bigint<\/code>.<\/p>\n\n\n\nThe default data type is bigint<\/code>. In other words, if we don’t specify the data type, the sequence will use bigint<\/code>. This has implications for the default MAXVALUE<\/code> and MINVALUE<\/code>. The default maximum value for an ascending sequence is the maximum value of the data type. The default minimum value for a descending sequence is the minimum value of the data type.<\/p>\n\n\n\nLet’s create some more sequences:<\/p>\n\n\n\n
CREATE SEQUENCE Sequence3 AS integer\n INCREMENT 1;\nCREATE SEQUENCE Sequence4 AS integer\n INCREMENT -1;<\/code><\/pre>\n\n\n\nIn this case, both sequences use the integer<\/code> type, but they use a different increment. Specifically, Sequence3<\/code> uses a positive increment, while Sequence4<\/code> uses a negative increment.<\/p>\n\n\n\nNext we’ll see how this affects the MINVALUE<\/code> and MAXVALUE<\/code> for each sequence.<\/p>\n\n\n\nCheck the Sequences<\/h2>\n\n\n\n
We can run the following query to get information about our sequences:<\/p>\n\n\n\n
SELECT \n pc.relname,\n ps.seqstart,\n ps.seqincrement,\n ps.seqmax,\n ps.seqmin,\n ps.seqcache,\n ps.seqcycle\nFROM pg_class pc\nJOIN pg_sequence ps \nON pc.oid = ps.seqrelid\nWHERE pc.relname LIKE 'sequence%';<\/code><\/pre>\n\n\n\nResult:<\/p>\n\n\n\n
relname | seqstart | seqincrement | seqmax | seqmin | seqcache | seqcycle \n-----------+----------+--------------+---------------------+-------------+----------+----------\n sequence1 | 1 | 1 | 9223372036854775807 | 1 | 1 | f\n sequence2 | 101 | 10 | 250000 | 100 | 1 | f\n sequence3 | 1 | 1 | 2147483647 | 1 | 1 | f\n sequence4 | -1 | -1 | -1 | -2147483648 | 1 | f<\/pre>\n\n\n\nWe can see the differences between each of the sequences. In particular, we can see how sequence3<\/code> and sequence4<\/code> differ, based on the increments that we gave them in the previous example (i.e. one has a positive increment, the other has a negative increment).<\/p>\n\n\n\nRegarding sequence1<\/code>, the seqmax<\/code> (maximum value) reflects the bigint<\/code> type. That’s because we didn’t specify a data type for that sequence, and so it used the default type (which is bigint<\/code>). For sequence2<\/code>, we specified our own MAXVALUE<\/code> of 250000<\/code>, and so this is reflected in the seqmax<\/code> column. We can also see that some of its other properties reflect the values that we gave the sequence when we created it.<\/p>\n","protected":false},"excerpt":{"rendered":"PostgreSQL allows us to create sequence objects, otherwise known as “sequence generators”, or simply “sequences”. As the name suggests, a sequence object is used to generate sequence numbers. We can use sequences to generate incrementing numbers or decrementing numbers.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[40],"tags":[10,164],"class_list":["post-29700","post","type-post","status-publish","format-standard","hentry","category-postgresql","tag-how-to","tag-sequences"],"_links":{"self":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/29700","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/comments?post=29700"}],"version-history":[{"count":5,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/29700\/revisions"}],"predecessor-version":[{"id":29834,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/29700\/revisions\/29834"}],"wp:attachment":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/media?parent=29700"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/categories?post=29700"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/tags?post=29700"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}