Questions tagged [sequence]

An object that can generate unique integers.

Filter by
Sorted by
Tagged with
36
votes
2answers
4k views

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 ...
6
votes
1answer
21k views

The InnoDB log sequence number is in the future

Our site was getting intermittent database errors and I asked my webhost to check what's happening. After some inspection they found that the database has few issues and tried repairing it. Finally I ...
3
votes
1answer
6k views

PostgreSQL SELECT primary key as "serial" or "bigserial"

I've meshed together a way to determine what the data_type is as in the data_type you use in the syntax when creating a new table based off of the PostgreSQL wiki page. If there is something wrong ...
23
votes
2answers
52k views

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 | +---...
9
votes
1answer
3k views

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 ...
4
votes
1answer
2k views

CREATE SEQUENCE using expressions with psql variables for parameters

I've been playing with Postgres for a week or so and I'm seeing if it's possible to set the min/max values for a sequence using an expression. The specific goal is to automate create-scripts for ...
4
votes
1answer
9k views

Permission for sequence in another schema

Postgres 9.3 Debian 7.0 I created a specific schema for a specific user and created a view in this schema for this user, so it's the only table he knows that exists. The problem is that this same ...
6
votes
3answers
4k views

Prevent explicit insert into serial columns

I want to prevent explicit inserts into serial columns. I have come up to the following trigger: drop table test_table; create table test_table( id bigserial primary key, foobar text ); create or ...
4
votes
3answers
5k views

Copy table with separate sequences for serial columns

While creating a column management tool I came across the need to quickly replicate tables in PostgreSQL so I would not test new tools with non-test tables. To effectively test the new column tool ...
3
votes
3answers
3k views

Create unique sequence per set of rows sharing the same foreign key value

In my system, I want each user to have an own id sequence for his articles. So, I have created a database that has a table that depicts a correspondence between users and their articles. Here is the ...
6
votes
1answer
1k views

Guarantee SQL Server Identity Columns that two consecutive values differ by constant increments?

I posed a question about Denali sequences here and another question about emulation of identity columns in Oracle Global Tables here. I always thought that I could trust that identity values ...
13
votes
4answers
5k views

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 ...
10
votes
1answer
14k views

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 ...
4
votes
3answers
463 views

Achieving "sequence number", rather than dateTime, for updates; postgres specifically

So a postgres database (specifically, an AmazonRDS instance fwiw). It's easy enough to arrange things to get the last updated (or created) time for any record. That information would be a datetime. ...
0
votes
1answer
217 views

Database Blocking [closed]

We have been using SQL Server 2014 Enterprise Edition for one of our applications and have been experiencing severe blocking the past few days. This application is supported by a vendor and hence we ...
82
votes
10answers
275k views

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'...
12
votes
1answer
28k views

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 (...
9
votes
1answer
3k views

How can I implement a sequence for each foreign key value?

In a PostgreSQL (9.3) database, I have a one-to-many relationship between Accounts and Customers, so within the Customers table, I have a globally-unique id for the primary key of each Customer, plus ...
2
votes
2answers
2k views

Custom Sequences with SQL Server

What is the best method to generate a Custom Auto-Generated Sequence with SQL Server? Here is my requirement: My application is hosted on 10 independent servers which are not interconnected. Each ...
1
vote
3answers
1k views

Auto-incremented ID with sequence and trigger: How to handle manual overrides (causing non-unique error)

I have a sequence and trigger that provide auto-incrementing IDs to a PROJECT_ID column: PROJECTS +------------+-----+ | PROJECT_ID | ... | +------------+-----+ | LC000001 | | | LC000002 | ...
8
votes
2answers
10k views

postgres sequence problems, manually created pids, and proper sequence resetting

Developers encountered issues with sequences in our postgres 9.6 database over the past year, where they stop working, so they have resorted to things like inserting rows with a pid manually created ...
0
votes
1answer
219 views

A (simple?) solution to the "logical decoding / sequence" problem in Postgres?

After reading this mind-blowing answer by Craig Ringer to my previous question Achieving "sequence number", rather than dateTime, for updates; postgres specifically I've become interested in ...
0
votes
1answer
1k views

Use serial ID for multiple columns and return it from INSERT

I am using Npgsql and I want to be able to do an insert, and if statement that will do an update and then return a value so when ExecuteScalar is called it reads that value. This was done fine in ...