Questions tagged [sequence]

An object that can generate unique integers.

Filter by
Sorted by
Tagged with
1
vote
1answer
33 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
28 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
25 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
33 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
63 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
23 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
432 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
1answer
107 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
30 views

step interpolation

My data: +------------+-------+ | date | value | +------------+-------+ | 2018-12-01 | 10 | | 2018-12-09 | 15 | | 2018-12-17 | 12 | +------------+-------+ My goal: +------------+-----...
0
votes
2answers
378 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
638 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
18 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
141 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
59 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
120 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
339 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
125 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
127 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
692 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
247 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
30 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
686 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
738 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: ...
7
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
5k 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 ...
3
votes
1answer
7k 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
293 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
547 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
118 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
3k 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
4k 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
689 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
9k 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
2k 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
121 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
4k 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
127 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
36 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
651 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 ( ...
4
votes
3answers
2k 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
170 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
316 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
238 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 ...
0
votes
1answer
203 views

Using `nextval` and `currval` inside one CREATE TABLE

Let's say that we would like to model a team of people that can have subteams like so: CREATE SEQUENCE seq_teams; CREATE TABLE teams ( tid INTEGER PRIMARY KEY DEFAULT nextval('seq_teams'), ...
25
votes
1answer
4k views

Sequence - NO CACHE vs CACHE 1

Is there any difference between a SEQUENCE declared using NO CACHE and one declared using CACHE 1 in SQL Server 2012+? Sequence #1: CREATE SEQUENCE dbo.MySeqCache1 AS INT START WITH 1 ...
3
votes
1answer
1k views

CREATE SEQUENCE using expressions with psql variables for parameters

I've been playing with Postgres for a week or so and I'm seeing if it's possible to set the min/max values for a sequence using an expression. The specific goal is to automate create-scripts for ...
0
votes
1answer
441 views

Trigger function - PostgreSQL 9.2

The idea: 1 - The customer can add any value into users.code column 2 - If the customer does not insert data into it, the trigger/funciton has to do the job I am able to do that, using this SQL: ...
0
votes
0answers
206 views

NEXT VALUE FOR SEQUENCE inside a CASE Statement

I want to use a new SEQUENCE each year (See here: 'Id' with the format: YYYYNNNNNN with the NNNNNN part restarting each year ) However you can not use NEXT VALUE FOR inside a CASE statement. ...
3
votes
3answers
1k views

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

In my system, I want each user to have an own id sequence for his articles. So, I have created a database that has a table that depicts a correspondence between users and their articles. Here is the ...