Questions tagged [sequence]

An object that can generate unique integers.

Filter by
Sorted by
Tagged with
0
votes
0answers
10 views

What's a good way to alter default privileges to all the tables and sequences in one schema?

I created a role as CREATE ROLE readwrite; Grant some privileges in main schema: CREATE SCHEMA main; GRANT SELECT, INSERT, DELETE, UPDATE, TRUNCATE ON ALL TABLES IN SCHEMA main TO readwrite; Set ...
0
votes
1answer
91 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-...
2
votes
2answers
120 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: ...
0
votes
1answer
25 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 ...
1
vote
2answers
88 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 ...
3
votes
1answer
111 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 ...
0
votes
1answer
65 views

Export Postgres sequences only

Is there a way using PG_DUMP or any other commands to export only sequences from an existing database?
0
votes
1answer
26 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 ...
1
vote
1answer
69 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?
3
votes
1answer
47 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, ...
0
votes
3answers
504 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 ...
0
votes
2answers
52 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 ...
1
vote
1answer
34 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 ...
0
votes
1answer
54 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)?
0
votes
1answer
39 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 ...
2
votes
3answers
281 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 ...
1
vote
0answers
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 ...
0
votes
1answer
110 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 ...
4
votes
1answer
70 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 ...
4
votes
1answer
369 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 ...
0
votes
0answers
118 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....
0
votes
1answer
339 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) ...
0
votes
1answer
222 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 "(...
8
votes
2answers
893 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 ...
2
votes
1answer
295 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 ...
1
vote
1answer
151 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 ...
4
votes
2answers
739 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 ...
-1
votes
2answers
145 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 ...
1
vote
1answer
23 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 ...
0
votes
1answer
147 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 ...
5
votes
1answer
10k 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 ...
1
vote
0answers
47 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 ...
1
vote
1answer
1k 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 ║ ╠════════════╬═════════════╬═══════════...
-2
votes
2answers
62 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], [...
0
votes
1answer
32 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 ...
0
votes
1answer
822 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 ...
1
vote
1answer
388 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 ...
0
votes
1answer
20k 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 ...
0
votes
2answers
76 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?
1
vote
1answer
180 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 ...
1
vote
1answer
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 (...
0
votes
1answer
29 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 ...
3
votes
2answers
7k 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 ...
0
votes
2answers
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. ...
2
votes
1answer
48 views

step interpolation

My data: +------------+-------+ | date | value | +------------+-------+ | 2018-12-01 | 10 | | 2018-12-09 | 15 | | 2018-12-17 | 12 | +------------+-------+ My goal: +------------+-----...
1
vote
2answers
3k views

how to create custom id in sql server with sequence

How can I create a table with custom ID like BK-0001001 with a sequence. Sorry I'm a newbie with databases. All help will be appreciated thank you!
2
votes
6answers
4k views

SELECT all rows with sequential id after a specific id

Is it possible to query all rows that their ids are sequential after a specific id? (id is an AUTO_INCREMENT column.) For example, suppose my specific id is 5. Then I want all rows with these ids: 6, ...
0
votes
1answer
25 views

Counting entries of a unique entry of another field and concatenate the result with the unique field in a new column [closed]

I want to create a query that will search the table and find the following: If a POL_N entry has ENDO_N entries then the COUNT ENDO_N field will start counting the ENDO_N entries and will get the ...
-2
votes
2answers
634 views

Adding sequence number to existing rows based on ID and Date in PLSQL Oracle [closed]

I want to add a sequence number (SEQNO) based on their REFNO and STARTD.
1
vote
0answers
149 views

Sequence: last_value billions bigger that actual count of rows

In my PostgreSQL 9.6 database, INSERT on a certain table is not possible anymore: The primary key column is an integer and the last_value of the sequence in question is 2,147,484,010 - which is too ...