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.

200 questions
86
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
  • 33,945
  • 27
  • 62
  • 64
45
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…
Youcef LAIDANI
  • 553
  • 1
  • 4
  • 10
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
  • 12,369
  • 24
  • 79
  • 111
25
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
  • 767
  • 3
  • 8
  • 10
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
  • 9,052
  • 6
  • 46
  • 52
20
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
  • 395
  • 1
  • 3
  • 9
17
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
  • 373
  • 1
  • 3
  • 12
17
votes
3 answers

Emulate a TSQL sequence via a stored procedure

I have a requirement to create a stored procedure which emulates a TSQL sequence. That is it always gives an increasing distinct integer value on every call. In addition, if an integer is passed in it should return that value if there has never…
Hogan
  • 540
  • 1
  • 6
  • 15
17
votes
3 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
  • 357
  • 1
  • 3
  • 9
17
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
  • 283
  • 2
  • 5
15
votes
1 answer

Restarting identity columns in Postgresql

For serial columns used in Postgresql < 10, we manage the sequence by its name. We were able to reset a sequence with: SELECT setval('table_id_seq', (SELECT MAX(id) FROM table)); From version 10, using identity columns, there is no need to use the…
jgrocha
  • 395
  • 1
  • 6
  • 11
14
votes
2 answers

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
  • 243
  • 1
  • 2
  • 7
14
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
  • 303
  • 1
  • 2
  • 6
13
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,533
  • 7
  • 28
  • 22
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,550
  • 5
  • 31
  • 56
1
2 3
…
13 14