Database Administrators Stack Exchange
Database Administrators Stack Exchange

Questions tagged [sequence]

An object that can generate unique integers.

An object that can generate unique integers. Usually these are sequentially increasing numbers, but could be decreasing, incrementing by more than 1 for each, etc.

Normally this is used to provide a highly scalable and well-performing method for generating surrogate keys. Note that a sequence could potentially have "gaps" (particularly Oracle) due to caching of generated sequences of numbers.

172 questions
85
votes
10 answers

How do I use currval() in PostgreSQL to get the last inserted id?

I have a table: CREATE TABLE names (id serial, name varchar(20)) I want the "last inserted id" from that table, without using RETURNING id on insert. There seem to be a function CURRVAL(), but I don't understand how to use it. I have tried…
Jonas
  • 29,165
  • 26
  • 57
  • 64
36
votes
2 answers

Why are Denali sequences supposed to perform better than identity columns?

In his answer to Which is better: identity columns or generated unique id values? mrdenny says: When SQL Denali comes out it will support sequences which will be more efficient than identity, but you can't create something more efficient…
bernd_k
  • 11,741
  • 23
  • 73
  • 108
25
votes
1 answer

Sequence - NO CACHE vs CACHE 1

Is there any difference between a SEQUENCE declared using NO CACHE and one declared using CACHE 1 in SQL Server 2012+? Sequence #1: CREATE SEQUENCE dbo.MySeqCache1 AS INT START WITH 1 INCREMENT BY 1 MINVALUE 1 MAXVALUE 9999 NO…
marc_s
  • 8,613
  • 6
  • 43
  • 51
23
votes
2 answers

How to generate a sequence in mysql

Consider this table in mysql create table numbers (number int); insert into numbers values (3), (2), (9); select * from numbers; +--------+ | number | +--------+ | 3 | | 2 | | 9 | +--------+ Is there a simple query to generate a…
sjdh
  • 695
  • 3
  • 7
  • 10
21
votes
1 answer

PostgreSQL - Truncate a table on cascade and reset all hierarchic sequences with 1

Is there any way to reset all the sequences of tables, when truncate a table on cascade. I already read this post How to reset sequence in postgres and fill id column with new data? ALTER SEQUENCE seq RESTART WITH 1; UPDATE t SET…
YCF_L
  • 313
  • 1
  • 2
  • 8
17
votes
1 answer

Fixing table structure to avoid `Error: duplicate key value violates unique constraint`

I have a table which is created this way: -- -- Table: #__content -- CREATE TABLE "jos_content" ( "id" serial NOT NULL, "asset_id" bigint DEFAULT 0 NOT NULL, ... "xreference" varchar(50) DEFAULT '' NOT NULL, PRIMARY KEY ("id") ); Later…
Valentin Despa
  • 365
  • 1
  • 3
  • 9
14
votes
2 answers

Resetting a SQL Server 2012 sequence

I'm in the process of testing and populating a specific table that leverages the SEQUENCE object. In this process I'm testing populating the table with tens of thousands of insert lines (as I'm unfamiliar with how to program this). The problem I'm…
Techie Joe
  • 343
  • 1
  • 2
  • 12
13
votes
4 answers

What can go wrong using the same sequence across multiple tables in postgres?

We are considering using a shared sequence to assign ids to primary keys for all of the tables in our database. There are about 100 of them. Only a couple are inserted to frequently and regularly. We want to rule out it being "a terrible idea for an…
Burleigh Bear
  • 243
  • 2
  • 5
12
votes
1 answer

Sequence is reusing

I have a sequence that generates tracking numbers for objects in my system. It had been working fine for quite some time. Last week we noticed that it was starting to re-use values. What seems to happen is that at different points in the evening,…
Vaccano
  • 2,324
  • 3
  • 27
  • 44
12
votes
1 answer

Most efficient way to add a serial column to a huge table

What's the fastest way to add a BIGSERIAL column to a huge table (~3 Bil. rows, ~ 174Gb)? EDIT: I want the column to be incremented values for existing rows (NOT NULL). I didn't set a fillfactor (which looks like a bad decision in retrospect). I…
Thi Duong Nguyen
  • 283
  • 1
  • 2
  • 6
11
votes
3 answers

How do I create a table with a column that uses a sequence?

I have the following CREATE TABLE [MyTable] ( [ID] [bigint] PRIMARY KEY NOT NULL, [Title] [nvarchar](64) NOT NULL ) CREATE SEQUENCE MyTableID START WITH 1 INCREMENT BY 1 NO CACHE ; GO I want to insert new records on MyTable…
BrunoLM
  • 3,043
  • 7
  • 25
  • 22
11
votes
3 answers

How could sequence.nextval be null in Oracle?

I have an Oracle sequence defined like so: CREATE SEQUENCE "DALLAS"."X_SEQ" MINVALUE 0 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 0 NOCACHE NOORDER NOCYCLE ; It is used in a stored procedure to insert a…
Corbin March
  • 213
  • 1
  • 2
  • 7
10
votes
1 answer

Explicitly granting permissions to update the sequence for a serial column necessary?

Recently I did create a table as a superuser including a serial id column, e.g., create table my_table ( id serial primary key, data integer ); As I wanted my non-superuser user to have write access to that table, I granted it…
moooeeeep
  • 203
  • 1
  • 2
  • 7
10
votes
2 answers

Postgres: Get nextval in sequence without actually incrementing sequence?

It looks like select nextval('table_name') actually does the value increment. My goal is to "predict" the nextval value on all tables in the database without actually making any incrementation. This should be a read-only operation. I cannot run…
emmdee
  • 277
  • 1
  • 3
  • 9
10
votes
1 answer

Compacting a sequence in PostgreSQL

I have a id serial PRIMARY KEY column in a PostgreSQL table. Many ids are missing because I have deleted the corresponding row. Now I want to "compact" the table by restarting the sequence and reassigning the ids in such a way that the original id…
rubik
  • 515
  • 7
  • 13
1
2 3
…
11 12