Questions tagged [sequence]

An object that can generate unique integers.

Filter by
Sorted by
Tagged with
0
votes
1answer
39 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
42 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
91 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
1answer
48 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
22 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
0answers
128 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
57 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
317 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
34 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
45 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 ...
0
votes
1answer
221 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
27 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
2k 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
377 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
34 views

step interpolation

My data: +------------+-------+ | date | value | +------------+-------+ | 2018-12-01 | 10 | | 2018-12-09 | 15 | | 2018-12-17 | 12 | +------------+-------+ My goal: +------------+-----...
0
votes
2answers
829 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
2k 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: ...
0
votes
1answer
24 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
294 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
87 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 ...
3
votes
2answers
175 views

Max number of sequence objects

At my job we have a lot of objects that we track versions of. We are tentatively looking at creating a rather large number of sequence objects in order to keep track of these version. My concern is ...
2
votes
2answers
481 views

Oracle sequences equivalent in MySQL

I did ask same question on stackoverflow, but no response, hence I thought database experts will help me to solve this. I have following table similar to Oracle user_sequences. I have logic of ...
0
votes
1answer
158 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 ...
1
vote
0answers
253 views

Can I make a sequence skip certain values?

In SQL server 2014 we are using sequences for some integer surrogate keys. They begin in the negative values counting up. Can we program the sequence to skip zero when the time comes? The reason is ...
3
votes
1answer
1k views

Postgres, how insert “0” value into serial?

I have a serial id in my application table. However, I would like to insert a single 0 id record to mean global. Is there a way I can insert this into my table without affecting the counter?
3
votes
2answers
435 views

How to retain the current value of a sequence when copying a database in SQL Server

I have a template database that contains tables with some data I'd like to have always when starting a new project. The idea is to copy the template when starting a new project. I use sequences for ...
1
vote
0answers
36 views

Oracle streams how exclude chages in sequences in schema replication

I have two DBs I want to synchronize using streams So I followed N-way replication guide. I have following config: BEGIN DBMS_STREAMS_ADM.ADD_SCHEMA_RULES( schema_name => '...
1
vote
1answer
891 views

Custom Sequence using Trigger & Sequence in Oracle SQL

I have a trigger which inserts a custom Sequence after every Parent Item is inserted. Now I have a sub-Item which is linked to the Parent Item through Parent Item Id (Sequence and Parent Item Id are 2 ...
4
votes
2answers
1k views

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

I have renamed a sequence with the following command: ALTER SEQUENCE TableIdSeq RENAME TO NewTableIdSeq; however, when I issue the \d NewTableIdSeq command, I get the following output: ...
8
votes
4answers
2k 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 ...
0
votes
1answer
7k views

How to fix all duplicate key value violates unique constraint

I have a Postgres database that I created with a Django application. When I transferred it over, it never reindexed anything and it gives me errors trying to do this. It seems as if there are lots of ...
7
votes
1answer
13k views

PostgreSQL - Truncate a table on cascade and reset all hierarchic sequences with 1

Is there any way to reset all the sequences of tables, when truncate a table on cascade. I already read this post How to reset sequence in postgres and fill id column with new data? ALTER SEQUENCE ...
1
vote
1answer
423 views

Error granting access to sequence with PostgreSQL 8.4

I'm attempting to run this query GRANT USAGE ON [db].users_uid_seq TO [user]; db and user are redacted (obviously). I get this error: ERROR: syntax error at or near "GRANT" I've tried a number ...
2
votes
2answers
813 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
1answer
125 views

Number Sequence With Gaps

In an Oracle PLSQL function I want to generate a special kind of sequence number. It starts with 00000001, but when 00899999 is reached, I want to have 01000001 as its next number. This pattern ...
4
votes
3answers
4k views

How to determine which tables use a sequence in SQL Server

I have a sequence in a SQL Server 2012 database that appears in sys.sequences. How do I determine which tables use this sequence? I do not have sufficient permissions to view the definition of this ...
6
votes
2answers
6k 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 ...
1
vote
3answers
951 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 | ...
0
votes
1answer
31 views

SQL Server 2012 - Know when a SEQUENCE has recycled or is nearing cycle phase

In SQL Server 2012, is there a way to know if a Sequence has recycled? Or some sort of trigger to know if a Sequence is nearing its cycling phase. I need to update a table whenever a ...
3
votes
1answer
13k views

INSERT INTO table FROM SELECT * with nextval() specification

I need to copy records within the same table changing only one field. My table has default-generated sequence entry_id_seq, however I am not sure id column is SERIAL (how to check that?). \d tab ...
0
votes
1answer
3k views

How do i assign a custom id using a trigger using sequence on a table

i want to modify my current table which is currently generating id's using a trigger from 1 using sequence_name.nextval to something like DON-100 or REC-124 etc.I have seen some examples doing this ...
1
vote
1answer
171 views

Is it possible to enter a missing sequence number into another table, on exception in a Trigger

Below is the code which I have written in the exception part of my before insert trigger. CREATE OR REPLACE TRIGGER ORGANIZATIONS_TAB_BI_TRG BEFORE INSERT ON ORGANIZATIONS_TAB FOR EACH ROW ...
5
votes
1answer
6k views

How to get the sequence name for a serial column

As far as I can tell, this query should show the expression for the new value of a serial column: select d.adsrc from ( SELECT a.attrelid, a.attnum, n.nspname, c.relname, a.attname FROM pg_catalog....
3
votes
1answer
165 views

SERIALIZABLE transaction information leak via serial

I'm trying to implement a deterministic configuration for database sessions, and one problem seems to be with sequences. The following example demonstrates: CREATE TABLE foo (id serial, n integer); -...
2
votes
2answers
37 views

Does anyone ever use database sequences that count down from 2^64-1?

Does anyone ever use database sequences that count down from 2^64-1, rather than up from 1, for assigning ids to rows? The motivation for doing this would be to ensure that any application consuming ...
2
votes
2answers
1k views

INSERT rows in two tables preserving connection to a third table

As part of a recent database development project, I'm stuck with a query for which I seem to find no elegant solution (i.e. without looping). Relevant bits of the schema: CREATE TABLE sites ( ...
5
votes
3answers
3k views

Reset a sequence every year

Every time a new row is added to the table below, I want the sequence (Import Permit No) to be increased by 1....20160001, 20160002, etc and reset to 20170001, 20170002, etc in a new year. CREATE ...
0
votes
1answer
181 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 ...
4
votes
3answers
355 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. ...
2
votes
0answers
274 views

Ensuring that Oracle sequence does not violate Java Integer constraints

Take for example a standard primary key on a JPA entity (POJO), similar to what is described by the first section of Configuring a JPA Entity Primary Key in the Oracle® Containers for J2EE Enterprise ...