Skip to main content
Share Your Experience: Take the 2024 Developer Survey
24 votes
Accepted

Sequence - NO CACHE vs CACHE 1

It is hard to give a definitive answer on this question until you actually find a difference. I have found none but that does not mean there is no difference only that I have not seen one in the tests ...
Mikael Eriksson's user avatar
17 votes
Accepted

Does ordering by auto-incrementing PK ensure chronological order?

Assuming by "auto-incrementing" you mean the Postgres SERIAL pseudo-type, the short answer is "not always". SERIAL columns are implemented using standard SQL sequences, which might generate out-of-...
mustaccio's user avatar
  • 26.2k
16 votes
Accepted

Postgres: Get nextval in sequence without actually incrementing sequence?

I would assume that SELECT currval('seq_name') + 1 or SELECT last_value + 1 FROM seq_name would be the answer, but that's only if you're incrementing by 1, or you know the increment off-hand. If you'...
richyen's user avatar
  • 780
12 votes
Accepted

Change LAST_NUMBER value in a sequence (NEXTVAL)

Change increment by, select next value, set increment back to original value: SQL> create sequence s1 start with 5; Sequence created. SQL> select last_number from user_sequences where ...
Balazs Papp's user avatar
  • 40.7k
11 votes

How to generate a sequence in mysql

With MySQL 8.0, MariaDB 10.2, and later versions, you can use recursive CTEs, so: WITH RECURSIVE nums AS ( SELECT 1 AS value UNION ALL SELECT value + 1 AS value FROM nums WHERE ...
dbdemon's user avatar
  • 6,371
11 votes
Accepted

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

This has been asked many times. The long and the short of it: Don't try it. You'd run into all kinds of problems with concurrency, gaps introduced by deleting rows, etc. Make id a plain serial column ...
Erwin Brandstetter's user avatar
11 votes

How to safely replace the old table with its cleaned copy?

By default, the error message tells you more. Like: ERROR: cannot drop table tbl because other objects depend on it SQL state: 2BP01 Detail: view v_tbl depends on table tbl Hint: Use DROP ... CASCADE ...
Erwin Brandstetter's user avatar
9 votes
Accepted

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

As a_horse_with_no_name originally suggested in question comments: --assuming we have tables: a, b, ... CREATE TABLE a ( a_id integer NOT NULL PRIMARY KEY, a_name text ) ; CREATE TABLE b ( ...
ypercubeᵀᴹ's user avatar
9 votes
Accepted

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

Three possible issues that spring to mind are: With any shared resource you are creating a potential bottleneck. My gut says that for your peak load this should not be an issue but I strongly suggest ...
David Spillett's user avatar
9 votes

Why is the serial primary key incrementing despite a using "On Conflict Do Nothing?

Use MERGE instead? MERGE (added with Postgres 15) looks similar on the surface, but is quite different under the hood. The manual: When MERGE is run concurrently with other commands that modify the ...
Erwin Brandstetter's user avatar
8 votes

How to fix all duplicate key values from sequences?

The code you display is not related to "reindexing". (Compare: REINDEX). It gets a sequence behind a serial column (or IDENTITY column in Postgres 10 or later) back in sync - by setting it ...
Erwin Brandstetter's user avatar
8 votes
Accepted

How to auto-increment a serial column ON UPDATE?

It's an unusual request, because one wouldn't typically want to change an existing serial ID. (The PK, no less!) But to answer the question: Use a trigger. Example trigger function & trigger: ...
Erwin Brandstetter's user avatar
7 votes
Accepted

How to get the sequence name for a serial column

In the comments, a_horse_with_no_name said: Are you using Greenplum or Postgres? In Postgres you can just use pg_get_serial_sequence You can find the docs here. pg_get_serial_sequence returns ...
Evan Carroll's user avatar
  • 63.6k
7 votes

How to determine which tables use a sequence in SQL Server

A quick test shows that sequence references are tracked via sys.dm_sql_referencing_entities in SQL Server 2014 SP2. I don't have access to SQL Server 2012, but you can use the following query to (have ...
Geoff Patterson's user avatar
7 votes
Accepted

Postgres, how insert "0" value into serial?

Sure, just do it. The counter only gets incremented when you call a Sequence Manipulation Function. In the case when you don't explicitly specify a value, the DEFAULT triggers a call to nextval(...
Evan Carroll's user avatar
  • 63.6k
6 votes
Accepted

INSERT INTO table FROM SELECT * with nextval() specification

As noted in the comments there is no special syntax for such task. You could to use combination of functions to_json(b), json(b)_set and json(b)_populate_record: --drop table if exists t; create ...
Abelisto's user avatar
  • 1,559
6 votes

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

Community Wiki answer recording comments on the question by Craig Ringer Quick warning about this. If you intend to do this so your app can remember the "last seen sequence number" and do some kind ...
6 votes
Accepted

Reset a sequence every year

In SQL Server 2012 or later, I would implement this using sequence objects. For SQL Server 2008 R2, my replacement for that missing feature is Sequence Tables. In this case, there would be a key in ...
Paul White's user avatar
  • 85.8k
6 votes

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

The reason they stopped working the first time is the well-known cause that a table was restored for example. However, at this point I think the manual code and sequences are stepping on each other, ...
Evan Carroll's user avatar
  • 63.6k
6 votes
Accepted

How to list all sequences by psql with postgres?

\ds without argument doesn't show sequences that are not accessible through the current search_path, whereas information_schema.sequences ignores the search path. On the other hand, information_schema....
Daniel Vérité's user avatar
5 votes
Accepted

CREATE SEQUENCE using expressions with psql variables for parameters

You need \set (not \pset!) to set psql variables. Unlike assignment in a Unix shell the psql assignment is not capable of arithmetic operations. You could use the psql command \! to execute shell ...
Erwin Brandstetter's user avatar
5 votes

Prevent explicit insert into serial columns

Identity Columns This isn't exactly an answer yet, but what you want is GENERATED ALWAYS. And, it's on its way, maybe as soon as the next release of PostgreSQL, PostgreSQL 10 CREATE TABLE itest4 ( ...
Evan Carroll's user avatar
  • 63.6k
5 votes

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

So there are some issues with these various methods: Currval only gets the last value generated in the current session - which is great if you don't have anything else generating values, but in cases ...
chander's user avatar
  • 161
5 votes
Accepted

How to determine which tables use a sequence in SQL Server

This will give you a list of constraints and their definitions which contain NEXT VALUE FOR, which implies they are a sequence. Then you can see the sequence name in the definition. SELECT ...
Ollie's user avatar
  • 738
5 votes
Accepted

Custom Sequences with SQL Server

Why don't you just use an actual SEQUENCE which was introduced to SQL Server with 2012? The following approach overloads the BIGINT value where the leading 10 digits represent the server IP address ...
John Eisbrener's user avatar
5 votes
Accepted

How can I change the sequence_name parameter after renaming a sequence (PostgreSQL)

Don’t worry about it. The sequence name stored inside the sequence is ignored. This field is gone in PostgreSQL 10.
Peter Eisentraut's user avatar
5 votes
Accepted

How can I safely rename a sequence in PostgreSQL, ideally without downtime?

If the sequence is used in the DEFAULT clause of the table column, it is enough to rename the sequence. That is because the DEFAULT clause is not stored as string, but as parsed expression tree (...
Laurenz Albe's user avatar
5 votes
Accepted

Are SqlServer Sequences, guaranteed to return different values for concurrent transactions?

Hence, in my case, there is no duplicate checking while inserting. If you really do not intend to allow duplicate values, you should create a UNIQUE constraint on the column where the ciphered text ...
Josh Darnell's user avatar
  • 29.4k

Only top scored, non community-wiki answers of a minimum length are eligible