Questions tagged [sequence]

An object that can generate unique integers.

Filter by
Sorted by
Tagged with
1 vote
1 answer
40 views

Can one dictate the order of generated values when adding a new BIGSERIAL column to a table?

I need to add a new BIGSERIAL column to a huge table (~3 billion records). This question is similar to what I need to do and the accepted answer has helped me somewhat. But I'm still wondering about ...
user avatar
0 votes
1 answer
77 views

Create Sequence Permission for Postgres

When my postgres function is tries to create a new sequence, it returns a permission denied error. It looks like the only way to make it work is to give Create permission on schema using the below ...
user avatar
0 votes
1 answer
42 views

Sequence not overflowing

I am trying to figure out how a table insert behaves when the corresponding sequence overflows in Postgres. To test this, I created a table CREATE TABLE t1( id SERIAL ); This created a ...
user avatar
  • 105
-2 votes
1 answer
29 views

What PostgreSQL query will return unused SEQUENCE #'s?

I have enabled a UNIQUE key so PostgreSQL would act as a proof reader for duplicate entries as a bunch of INSERT INTO statements. Now I need a list of the unused SEQUENCE numbers so I populate these ...
user avatar
0 votes
1 answer
92 views

XE_SERVICES_RWLOCK in top waits

We start facing some uncommon waits after the last release. XE_SERVICES_RWLOCK waits starts to appear in our top waits. Investigation shows that this waits happen during insert to our main OLTP tables....
user avatar
0 votes
0 answers
40 views

How to get the current value of sequence of a table in remote server using foreign data wrapper?

Remote server create table tempparent ( id BIGSERIAL NOT NULL, empname VARCHAR, CONSTRAINT uk_tempparent_id UNIQUE (id) ); create table tempchild ( id BIGSERIAL NOT NULL, parentid ...
user avatar
1 vote
1 answer
2k views

INSERT ... ON CONFLICT DO NOTHING: why is the serial number incremented?

I have a simple table as below in PostgreSQL 13: table name: newtable1 field type ----- ---- Seq bigserial code varchar Seq is the primary key (auto-...
user avatar
  • 19
2 votes
2 answers
163 views

weird behaviour of NVL(<Column_name>,Sequence.nextval) when the colummn has data?

I've created a sequence like this : CREATE SEQUENCE seq_test2 MINVALUE 0 MAXVALUE 999999999999999999999999999 START WITH 0 INCREMENT BY 1; Both of these two queries show me the number 0: ...
user avatar
  • 1,302
0 votes
1 answer
68 views

Custom Sequence - Postgres

Problem Statment: We have multiple tables containing a column having the name "Code", it is an identity column. Our application is running by our multiple locations/clients, and they are ...
user avatar
2 votes
2 answers
2k views

How to auto-increment a serial column ON UPDATE?

When creating a sequence in Postgres 10, how do you auto-increment on updates? (Not just assign the next higher number for the next inserted row.) For example, suppose I create the following table and ...
user avatar
4 votes
1 answer
227 views

Suggestion for nearly gap-less sequences in postgres

In PostgreSQL sequences are designed to have gaps for reasons mentioned in this post https://stackoverflow.com/questions/9984196/postgresql-gapless-sequences. My question is: could sequences not be ...
user avatar
  • 205
2 votes
2 answers
1k views

Export Postgres sequences only

Is there a way using PG_DUMP or any other commands to export only sequences from an existing database?
user avatar
  • 221
0 votes
1 answer
47 views

Get sequence value date?

I have an OOB system that has hundreds of tables. Each table has a ROWSTAMP column (integer) that gets populated by a trigger each time a record is created/updated. All the tables & triggers in ...
user avatar
  • 1,389
2 votes
1 answer
1k views

How to list all sequences by psql with postgres?

I tried \ds didn't get any relations data. But I use SELECT * FROM information_schema.sequences; can get all the data. Why doesn't \ds work?
user avatar
  • 157
5 votes
1 answer
1k views

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, ...
user avatar
  • 275
0 votes
3 answers
3k views

ERROR: currval of sequence "rss_sub_source_id_seq" is not yet defined in this session

When I used this query to see the current primary key seed of my PostgreSQL 13 table: select CURRVAL(pg_get_serial_sequence('rss_sub_source', 'id')) it told me: SQL Error [55000]: ERROR: currval of ...
user avatar
  • 477
0 votes
2 answers
169 views

Postgres latest sequence id and uncommitted writes

Hoping someone could clarify a section of this article "scalable-incremental-data-aggregation" and the corresponding question. Assume Postgres 10 and up. In Postgres 10, you can use the ...
user avatar
1 vote
1 answer
131 views

SSIS failure flow error. The decision after a sequence container failure does not proceed with the correct flow

I have an SSIS sequence container that extracts data from individual source database tables into a Stage layer. However, I need to know when a given calculation crashes in order for the given error to ...
user avatar
1 vote
1 answer
2k views

PL/SQL - how to identify sequences used by tables?

Is there a way to get a resultset from executing a query against an Oracle database that would return a listing of all the tables in the database and which sequence it is using (if any)?
user avatar
  • 133
0 votes
1 answer
284 views

Why GRANT UPDATE on SEQUENCE in PostgreSQL

I have seen postgresql allowing users to Grant update on a sequence. But never seen any update query on the sequence. Sequence values are usually changed using ALTER SEQUENCE command. I understand the ...
user avatar
2 votes
3 answers
282 views

If the maximum current value of an ID generated by a sequence is N, does that guarantee that all future rows will have index > N?

I'm debugging some legacy code and have found what might be a flaw in how it interacts with the database. A simplified version of it is as follows: TableA is acting as a queue. It has data inserted ...
user avatar
  • 121
1 vote
0 answers
31 views

Using one sequence across all tables in PostgreSQL [duplicate]

I propose using a single sequence for the bigint identity columns of most or all tables. (Essentially, OID, except 64-bit.) This helps prevent security problems for a multi-tenant application that ...
user avatar
0 votes
1 answer
506 views

Postgres: Find previous and next row or existing value created by SERIAL colum for separate query

I got a Postgres 13 DB and a table with an id based on a SERIAL. After deleting rows such a sequence would have gaps. In each row binary data is stored too and I would like to process it row after row ...
user avatar
0 votes
1 answer
306 views

Restoring schemas with the sequences

This evening a developer asked to restore only schemas from a database into another database. A colleague of mine did this, but after that the developer came to me saying that the sequences are ...
user avatar
4 votes
1 answer
105 views

Sequence object blocking

We have been experiencing an issue with a sequence object we have that is called a lot. It is used to assign a value prior to a record being inserted into a table. Today I saw that 700+ sessions (all ...
user avatar
  • 53
6 votes
1 answer
452 views

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

I have created a table_cleaned like table and inserted unique values from table into table_cleaned. Now I want to replace table with table_cleaned with DROP TABLE table; ALTER TABLE table_cleaned ...
user avatar
0 votes
0 answers
317 views

Migrating sequences from postgres to EDB database

Scenario : We are migrating data from a postgres DB to new EDB database . We have migrated the sequence and all table data also . Both DB is installed on a remote unix server and on diffrent machines....
user avatar
0 votes
1 answer
680 views

How to set current sequence while converting primary key serial(integer) to bigserial(bigint)

The table already has few thousands of rows and I want to convert the primary key from Integer to BigInteger. How to change the sequence for biginteger? --Create new temp sequence (sequence-name1) ...
user avatar
  • 1,788
1 vote
1 answer
777 views

Create sequence starting with maximum id from a table [duplicate]

I'm trying to create a sequence in the following way: create sequence some_seq start with (select max(id) + 1 from some_table); But I'm getting the following error: ERROR: syntax error at or near "(...
user avatar
8 votes
2 answers
1k views

Does ordering by auto-incrementing PK ensure chronological order?

Is it safe to assume that ordering by an auto-incrementing PK in Postgres will order the records chronologically, from a creation standpoint? I have a many to many relationship, from which I don't ...
user avatar
2 votes
1 answer
803 views

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

In documentation it is told that: Duplicate validation only occurs once a record is fully populated. This can result in some cases where the same number is used for more than one record during ...
user avatar
1 vote
1 answer
272 views

how to alter sequence in db2 stored procedure by another user

I am using DB2 LUW Version 10.5.0.5 on Linux (x86_64) I have written a procedure to alter a sequence to restart with a certain number. The procedure takes the sequence name and the number to restart ...
user avatar
  • 11
4 votes
2 answers
1k views

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

We want to rename some sequences in our production database for certain reasons. Is there a way to do this safely in production without having to first close all connections to the database? It's ...
user avatar
  • 187
-1 votes
2 answers
294 views

Generating millions of sequences in Postgres (with Redis)

I am creating a chat application, and I will save chat messages for all conversations and users in one partitioned (on convo ids), BRIN indexed table. When my server wants to store new messages it has ...
user avatar
  • 221
1 vote
1 answer
36 views

Postgres sequence get last usage

I have a database that has both sequenced correlated to table unique ids and "standalone" but having values. I need to know which ones are really used but I'm not the owner of every procedure running ...
user avatar
0 votes
1 answer
324 views

MariaDB, SEQUENCE engine, dynamic upper bound

I'd like to generate a sequence in MariaDB starting at 1 and ending with whatever is the largest id in some other table. That other table has roughly this schema: create table main ( id integer ...
user avatar
  • 135
12 votes
2 answers
19k views

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 ...
user avatar
  • 307
1 vote
0 answers
72 views

Sequence number for COMMITTED concurrent inserts

For example, I have a table create table Queue (OrderNum int, Data varchar(1000)) --int - may be a different type and many concurrent inserts into this table. Is there is a way to fill the OrderNum ...
user avatar
  • 119
1 vote
1 answer
3k views

Create sequence based on the value of another column

I have to create sequence numbers based on related user. Like: ╔════════════╦═════════════╦═══════════════════╗ ║ Row Id ║ User Id ║ Sequence Number ║ ╠════════════╬═════════════╬═══════════...
user avatar
-2 votes
2 answers
65 views

INSTEAD OF INSERT does not display changes

This is my table: CREATE TABLE [MyTable] ( [GroupId] INT NOT NULL, [ItemId] INT NOT NULL, [Data] INT NOT NULL, PRIMARY KEY CLUSTERED ([GroupId], [...
user avatar
  • 97
0 votes
1 answer
72 views

Postgres - how to investigate a dependency before DROPing a field

I want to drop a field from a partitioned table, POSTGRES claims there are dependencies and proposes using CASCADE. How can I check that nothing important will be deleted before risking DROP with ...
user avatar
0 votes
1 answer
1k views

Reset all sequences so that they continue after max(id) + 1?

It looks like I messed up a database migration and while all sequences are there, they would start at 1, which triggers errors like: django.db.utils.IntegrityError: duplicate key value violates ...
user avatar
  • 319
1 vote
1 answer
622 views

Determine Sequence last used value for sql server before 2017

I've got an issue with sql server before 2017. Its regarding current value and next value of a sequence. In 2017 you can determine based on the last_value_used that current_value is the last value or ...
user avatar
2 votes
2 answers
35k views

Change LAST_NUMBER value in a sequence (NEXTVAL)

My question seems easy but i haven't found a solution. I'm using the nexval sequence in a insert SQL script but we have errors because the LAST_NUMBER value haven't been updated and its value is not ...
user avatar
0 votes
2 answers
173 views

Sequence cache size [closed]

I have to change cache for all sequences in schema and database. I can not change one by one. Is there some script or query which can change cache size for all sequences?
user avatar
  • 1
1 vote
1 answer
381 views

Oracle - Sync Sequence nextval using CONNECT - side effects?

DB Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Action After a data migration - from an old table to a new table - we created a new sequence and executed this command to sync it's ...
user avatar
  • 125
1 vote
1 answer
1k views

How currval of sequence being preserved during the backup and restore in postgres

If I do backup and restore a huge db (tons of tables in it), will it run without any issues rather rising exception about duplicate key constraints of sequenced column(s). While taking pg_dump (...
user avatar
  • 715
0 votes
1 answer
42 views

Sequence according to value from table in postgresql [duplicate]

I have dictionary_type and dictionary_value tables. Value is connected with type. I would like to create sequence which will set the id of values according to type id. Expected dictionary_type: id ...
user avatar
  • 1
3 votes
2 answers
11k views

GRANT SELECT ON SEQUENCE succeeds, but to no effect?

I am trying to grant select access to sequences from one user/role to another. There are no errors when I run the command but once run, the second role is unable to view the sequences. I have run the ...
user avatar
0 votes
2 answers
1k views

Permissions on SQL Server Sequences

The AD Group to which I belong has db_owner permissions on database LandingZone. The application I am debugging, that is running under my ID, is doing a SELECT NEXT VALUE FOR ThisSchema.ThatSequence. ...
user avatar