Questions tagged [sequence]

An object that can generate unique integers.

Filter by
Sorted by
Tagged with
0
votes
1answer
98 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)?
12
votes
1answer
5k views

Sequence is reusing

I have a sequence that generates tracking numbers for objects in my system. It had been working fine for quite some time. Last week we noticed that it was starting to re-use values. What seems to ...
0
votes
1answer
878 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 ...
0
votes
0answers
18 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 ...
23
votes
2answers
52k views

How to generate a sequence in mysql

Consider this table in mysql create table numbers (number int); insert into numbers values (3), (2), (9); select * from numbers; +--------+ | number | +--------+ | 3 | | 2 | | 9 | +---...
6
votes
2answers
11k 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 ...
0
votes
1answer
126 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 ...
0
votes
1answer
280 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
124 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
27 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
240 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 ...
0
votes
1answer
31 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 ...
3
votes
1answer
128 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
210 views

Export Postgres sequences only

Is there a way using PG_DUMP or any other commands to export only sequences from an existing database?
1
vote
1answer
172 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?
2
votes
2answers
5k views

How to have one Auto Incremented id for two separate tables in mysql?

I have 2 different tables, each having its own id column. I need that if one table is using a specific id, the other table will not use it. This is needed because the object in both tables will later ...
3
votes
1answer
93 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
884 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
56 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
44 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 ...
-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
60 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 ...
3
votes
4answers
3k views

Primary key with randomly varying increments (so it cannot be guessed easily)

I would like the primary keys to be auto-incremented and generated but in varying increments. For ex, if I have increment range as 100... then the auto generated keys would be something like below: - ...
1
vote
2answers
12k 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 ...
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 ...
4
votes
1answer
71 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 ...
6
votes
3answers
4k views

Prevent explicit insert into serial columns

I want to prevent explicit inserts into serial columns. I have come up to the following trigger: drop table test_table; create table test_table( id bigserial primary key, foobar text ); create or ...
4
votes
1answer
371 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 ...
9
votes
1answer
3k views

Compacting a sequence in PostgreSQL

I have a id serial PRIMARY KEY column in a PostgreSQL table. Many ids are missing because I have deleted the corresponding row. Now I want to "compact" the table by restarting the sequence and ...
0
votes
0answers
147 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....
82
votes
10answers
275k views

How do I use currval() in PostgreSQL to get the last inserted id?

I have a table: CREATE TABLE names (id serial, name varchar(20)) I want the "last inserted id" from that table, without using RETURNING id on insert. There seem to be a function CURRVAL(), but I don'...
0
votes
1answer
388 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) ...
1
vote
1answer
322 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
915 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
386 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
180 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
781 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
159 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
24 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
184 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 ...
0
votes
1answer
26 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 ...
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!
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. ...
3
votes
1answer
6k views

PostgreSQL SELECT primary key as "serial" or "bigserial"

I've meshed together a way to determine what the data_type is as in the data_type you use in the syntax when creating a new table based off of the PostgreSQL wiki page. If there is something wrong ...
1
vote
0answers
51 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 ║ ╠════════════╬═════════════╬═══════════...
0
votes
1answer
33 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 ...
3
votes
2answers
1k 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 ...
4
votes
1answer
2k 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 ...