Stack Overflow
Stack Overflow

Questions tagged [database-sequence]

A database sequence is a number generator that produces unique numbers in a scalable and concurrency safe way.

A database sequence is a number generator that produces unique numbers in a scalable and concurrency safe way.

Once a sequence value has been generated, this number is never re-used (unless a sequence is defined to have a maximum value and cycle through its possible range).

Sequence values are not guaranteed to be gapless but offer a scalable and fast way to generate unique numbers even in high concurrency situations.

Sequences are supported by nearly all modern DBMS. However, the syntax to create them and to obtain a number from them differs between the DBMS products.

106 questions
4
votes
2 answers

Why this sequence increments by 2?

I can't understand why this sequence is incremented by 2. Is there any error in sequence to increment by 1? I need this to insert primary key value in table 'food'. CREATE SEQUENCE food_id_ai START WITH 1 INCREMENT BY 1 CACHE 100; create…
Masum
  • 145
  • 7
3
votes
1 answer

Sequence exists but I can't find it in information_schema.sequences (PostgreSQL)

I have created a sequence (let's call it my_seq) in a schema (let's call it my_schema) of my PostgreSQL (version 13) database. I am sure the sequence exists because I can find it in the result set of the query select n.nspname as sequence_schema, …
jPenF5
  • 41
  • 1
  • 2
3
votes
3 answers

Reset Postgres auto-increment value to 0

I'm having a table where I sometimes enter data in inline editors where I manually add the id value which is usually auto-incremented. In a case where id 4 was auto generated using scripts and then id 5 & 6 were added inline, whenever I run a insert…
Charith Jayasanka
  • 4,033
  • 31
  • 42
3
votes
1 answer

Why I am not able to refer to my sequence with double quotes in postgres?

I have created sequence in postgres. postgres=# create sequence my_sequence start 5 minvalue 3 increment 1 cycle; CREATE SEQUENCE Now I am trying to query the next value from the sequence. postgres=# select nextval("my_sequence"); ERROR: column…
Mangu Singh Rajpurohit
  • 10,806
  • 4
  • 68
  • 97
3
votes
1 answer

postgres how to reset or update index(sequence) of table

i deleted 2000 row in my table and then i inserted same 2000 records but their index(id auto increment field) starting from 2001, now i want to update those index 2001 - 4000 to 1-2000
DexJ
  • 1,264
  • 13
  • 24
2
votes
1 answer

PostgreSQL - Common autoincrement with inherited tables

I'm currently trying the inheritance system with PostgreSQL but I have a problem with the auto-increment index in my child tables. I have three tables: "Currency", "Crypto" and "Stable" CREATE TABLE IF NOT EXISTS public.currency ( id INT…
Naografix
  • 847
  • 2
  • 15
  • 35
2
votes
2 answers

Should I lock a PostgreSQL table when invoking setval for sequence with the max ID function?

I have the following SQL script which sets the sequence value corresponding to max value of the ID column: SELECT SETVAL('mytable_id_seq', COALESCE(MAX(id), 1)) FROM mytable; Should I lock 'mytable' in this case in order to prevent changing ID in a…
Artem Larin
  • 142
  • 6
2
votes
2 answers

Custom progressive sequence (per year) with a column as prefix

I need to create a custom sequence based on a specific column, added as a prefix. I know it is possible to customize the sequence as well as the nextval, but I'm not sure if it is possible to use the column of a specific table. This is the structure…
Francesco Simeoli
  • 541
  • 5
  • 19
2
votes
2 answers

Sequence neither SELECT nor CREATE in Oracle

I am facing a problem while using sequence in Oracle 11g Express Edition. It's neither accessible nor created. I tried this query to get NEXTVAL of sequence. select SEQ_PATIENT.nextval from dual; It displays error ORA-02289: sequence does not…
Muhammad Waheed
  • 1,048
  • 1
  • 13
  • 30
2
votes
1 answer

Oracle Sequences Out of Sync/Not Matching Table

We have an Oracle 11.2 database that has multiple, if not all, sequences out of sync. Their last number doesn't match the max number of their relevant database tables. I can't seem to find any possible causes online but one possibility that came to…
Paul S
  • 113
  • 1
  • 1
  • 13
2
votes
1 answer

In PostgreSQL, when granting sequences, can I grant only USAGE instead of both SELECT, USAGE?

I read answers about granting sequences in PostgreSQL. Generally, they say to grant both SELECT, USAGE. I wonder if I can grant only USAGE. Which one is best practice in granting sequences and why?
sandthorn
  • 2,770
  • 1
  • 15
  • 59
2
votes
1 answer

How to implement multidimensional sequences

For example, here is a yearly sequence. The no increments with year: | no | year | +----+------+ | 1 | 2016 | | 2 | 2016 | | 3 | 2016 | | 1 | 2017 | | 2 | 2017 | | 4 | 2016 | For now I have created sequence for each year but the problem is…
Charles Chou
  • 179
  • 1
  • 15
2
votes
0 answers

Reset PostgreSQL sequence in trigger function

I have a table counter_registry that has a column priority_number with its default value from nextval('counter_registry_priority_number_seq'::regclass). My trigger function (which runs before inserting) has a snippet that resets the sequence when…
Earl Lapura
  • 165
  • 2
  • 12
2
votes
1 answer

Insert sql statement with a subquery and sequence

I have an insert into statement and this statement will have a sub query where it gets all its information from. I just have one problem I have to use an primary key index which I created as a sequence. I just don't know how to insert a sequence…
Hendrien
  • 325
  • 1
  • 10
  • 20
1
vote
2 answers

How to create a PostgreSQL Table id Field and Define a Custom Sequence for It?

My table is defined as: CREATE TABLE accounts IF NOT EXISTS ( id SERIAL PRIMARY KEY, name VARCHAR(100) NOT NULL ); At some circumstances, I want to fetch next val of id before inserting data into a table at PostgreSQL. So, I created a…
kamaci
  • 72,915
  • 69
  • 228
  • 366
1
2
3 4 5 6 7 8