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 ...
- 22k
17
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'...
- 770
16
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-...
- 25.2k
14
votes
Accepted
Prevent explicit insert into serial columns
In Postgres 10 or later, consider an IDENTITY column instead. Like:
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY
See:
What are Identity Columns?
Auto increment table column
Original answer ...
- 172k
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 ...
- 172k
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 ...
- 6,261
11
votes
Accepted
How can I create a gapless sequence?
This results in unused sequence values
That's totally normal, and by design. It's also not the only way you can get such gaps. See the note on nextval in the manual.
Is there any way to reclaim ...
- 55.6k
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 ...
- 172k
10
votes
Accepted
How to get the next value of sequence?
You need to select from something. Another option is:
db2 => values next value for schema_name.sequence_name;
As you noted you can also use SYSIBM.SYSDUMMY1 or create a virtual table:
db2 => ...
- 23.1k
10
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 ...
- 40.4k
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
(
...
- 97.4k
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 ...
- 32k
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 ...
- 172k
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 ...
- 62.2k
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 ...
- 8,327
7
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:
...
- 172k
6
votes
How to get the next value of sequence?
db2 => select next value for schema_name.sequence_name from SYSIBM.SYSDUMMY1;
did the trick
- 365
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 ...
- 83k
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 ...
- 1,509
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, ...
- 62.2k
6
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(...
- 62.2k
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....
- 30.8k
5
votes
Accepted
SETVAL in a Window Function
Your usage of setval() is not what one usually expects. It is used for resetting the sequence. Feeding new values to IDs and other stuff is done by nextval(). Its function is described so:
...
- 31k
5
votes
Accepted
Getting the next bigserial value in PostgreSQL
I want to know what the id of the next row I insert will be. How can I tell this, without inserting a row?
This is exactly what nextval() does. But I can't think of any useful thing to do with this ...
- 97.4k
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 ...
- 172k
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 ...
- 161
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 (
...
- 62.2k
Only top scored, non community-wiki answers of a minimum length are eligible