PostgreSQL – How to Create A Sequence
You can create a sequence using the CREATE SEQUENCE command in PostgreSQL database. A sequence is typically used to generate unique integer values, often for primary keys or other situations where you need a unique identifier. Here’s the basic syntax to create a sequence:
CREATE SEQUENCE sequence_name [ INCREMENT increment ] [ MINVALUE min_value ] [ MAXVALUE max_value ] [ START start_value ] [ CACHE cache_size ] [ CYCLE | NO CYCLE ];sequence_name: This is the name you want to give to the sequence.INCREMENT increment: This option sets the increment value for the sequence. The default is 1, meaning that the sequence will increment by 1 each time a new value is generated.MINVALUE min_valueandMAXVALUE max_value: These options specify the minimum and maximum values for the sequence. By default, there is no minimum or maximum limit.START start_value: This option sets the initial value for the sequence. The default is 1.CACHE cache_size: Sequences can be cached to improve performance. This option specifies how many sequence values should be preallocated and stored in memory for faster access. The default is 1.CYCLEorNO CYCLE: By default, sequences stop generating values when they reach their maximum value. If you specifyCYCLE, the sequence will wrap around to its minimum value when it reaches the maximum value. If you specifyNO CYCLE, the sequence will stop generating values when it reaches the maximum value, and an error will occur if you try to get the next value.
CREATE SEQUENCE my_sequence;This creates a sequence named “my_sequence” with default settings.
You can also specify other options as needed based on your requirements. For example:
CREATE SEQUENCE order_id_seq INCREMENT 1 MINVALUE 1 MAXVALUE 10000 START 1001 CACHE 5 CYCLE;This creates a sequence named “order_id_seq” with specific settings, including a starting value of 1001, a maximum value of 10000, caching of 5 values, and cycling back to the minimum value when it reaches the maximum.