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
713
votes
34 answers

How to reset Postgres' primary key sequence when it falls out of sync?

I ran into the problem that my primary key sequence is not in sync with my table rows. That is, when I insert a new row I get a duplicate key error because the sequence implied in the serial datatype returns a number that already exists. It seems…
meleyal
  • 32,252
  • 24
  • 73
  • 79
334
votes
8 answers

Postgres manually alter sequence

I'm trying to set a sequence to a specific value. SELECT setval('payments_id_seq'), 21, true; This gives an error: ERROR: function setval(unknown) does not exist Using ALTER SEQUENCE doesn't seem to work either? ALTER SEQUENCE payments_id_seq…
stef
  • 26,771
  • 31
  • 105
  • 143
48
votes
2 answers

Sequences not affected by transactions?

I have a table create table testtable( testtable_rid serial not null, data integer not null, constraint pk_testtable primary key(testtable_rid) ); So lets say I do this code about 20 times: begin; insert into testtable (data) values…
Earlz
  • 62,085
  • 98
  • 303
  • 499
11
votes
3 answers

Why is an integer variable not accepted as a value for START WITH in sequence

I am trying to create dynamic start number for sequence but it is not accepting variable viz. @START_SEQ for START WITH. Please consider following code : - CREATE PROCEDURE [dbo].[SP_RESET_SEQ] AS DECLARE @START_SEQ INT =0; BEGIN SET @START_SEQ =…
RAKESH HOLKAR
  • 2,127
  • 5
  • 24
  • 42
10
votes
1 answer

Add auto increment column to existing table ordered by date

I have an existing table named "tickets" in database with columns: id (string, Primary Key, contains UUID like e6c49164-545a-43a1-845f-73c5163962f2) date (biginteger, stores epoch) status (string) I need to add new auto increment column…
Ashutosh
  • 4,371
  • 10
  • 59
  • 105
9
votes
3 answers

Db2: How to update the current value of a sequence

We use a sequence in a Db2 database. Recently, we have migrated the data from an AIX server to a Linux server. During that the latest number of that sequence was not moved to the Linux system. As a consequence, we are seeing duplicates values…
Sivapandian Jayakumar
  • 133
  • 1
  • 2
  • 10
8
votes
3 answers

ERROR: more than one owned sequence found in Postgres

I'm setting up a identity column to my existing columns for the Patient table. Here I would like to use GENERATED ALWAYS AS IDENTITY. So I setup the identity column by using the following statement (previously it was serial): ALTER TABLE Patient…
Nayan Rudani
  • 1,029
  • 3
  • 12
  • 21
8
votes
1 answer

Reset identity column with last value of table's identity in postgres

I would like to changes my existing column as Auto Identity in a Postgres Database. I used below script to set auto identity column and set default start with 1. But here i would like to reset identity column value to table's last value. Let me…
Nayan Rudani
  • 1,029
  • 3
  • 12
  • 21
7
votes
5 answers

Sequence does not exist when it does - Postgres/Spring Boot

I am writing a Spring Boot web-app and using a Postgres db to persist my data. I created a table in Postgres using create table user (id bigserial primary key not null, name text not null; and identified its sequence_name by looking at the schema…
ion20
  • 637
  • 4
  • 9
  • 21
7
votes
5 answers

How to create multiple sequences in one table?

I have a table "receipts". I have columns customer_id (who had the receipt) and receipt_number. The receipt_number should start on 1 for each customer and be a sequence. This means that customer_id and receipt_number will be unique. How can I…
David
  • 4,786
  • 11
  • 52
  • 80
6
votes
2 answers

Oracle 12.2 - Replacement of NOPARTITION feature

I have Oracle version 12.2.0.1.0 We have generic script which create sequence that need to be reuse for different objects (by renaming sequence name): CREATE SEQUENCE NAME_SEQ MINVALUE 1 MAXVALUE 999999999 INCREMENT BY 1 START WITH 100 CACHE 200…
Ori Marko
  • 56,308
  • 23
  • 131
  • 233
5
votes
3 answers

How to bulk update sequence ID postgreSQL for all tables

I imported the Postgres SQL file to my server using TablePlus(SQL client), but after I insert new row I got error like this: SQLSTATE[23505]: Unique violation: 7 ERROR: duplicate key value violates unique constraint \"users_pkey\" DETAIL: Key…
Muhammad Dyas Yaskur
  • 6,914
  • 10
  • 48
  • 73
5
votes
1 answer

Why does Postgres sequence item go up even if object creation fails?

I have a Postgres item where one of my models is Client simply indexed by its primary key. I was having an issue creating clients because somewhere along the lines someone created a client while explicitly setting its primary key which I have read…
TJB
  • 3,706
  • 9
  • 51
  • 102
5
votes
2 answers

Postgres insert value from insert in other table

I have two tables: CREATE TABLE tbl_a ( id serial primary key NOT NULL, name text NOT NULL, tbl_b_reference NOT NULL ) CREATE TABLE tbl_b ( id serial primary key NOT NULL, status text) I want to do two inserts. One in tbl_b, and then use the id…
Erik Einarsson
  • 53
  • 1
  • 6
5
votes
2 answers

Specify Oracle Sequence in SimpleJdbcInsert object to generate key from Oracle Sequence

I am using SimpleJdbcInsert as, SimpleJdbcInsert simpleJdbcInsert = new SimpleJdbcInsert(dataSource).withTableName(TABLE_NAME).withSchemaName(SCHEMA_NAME); Map namedParameterMap = new HashMap
SyParth
  • 211
  • 2
  • 7
1
2 3 4 5 6 7 8