Questions tagged [sequence]
An object that can generate unique integers.
187
questions
0
votes
1
answer
28
views
Postgresql Sequence Hopping
I have recently migrated (dump and restore) my database from AWS RDS PostgreSQL v12 to Aurora Serverless v2 PostgreSQL v14.6. The issue is we have setup sequencing in a table on old RDS, now in the ...
- 11
0
votes
2
answers
57
views
Exec An SQL query in a table in Database 'B' from database 'A'
i have this Table sequence_data in database A . The table has create script for sequences . No I want to create all these sequences in another database B. If it is the same database i can just use a ...
- 95
-3
votes
2
answers
71
views
What could cause an unexpected gap of 9,907 in an IDENTITY column with the cache size of 1,000?
In our production server (SQL Server 2017) my user reported a large gap of the sequential number (IDENTITY column) used to reference claims. The sequential was 4251290.
After researching this known ...
- 1
0
votes
1
answer
73
views
Script to get definitions of table types and Sequences
I need to get the definition of some objects in the SQL server. I got the definitions of stored procedures, functions, views and triggers from sys.sql_modules.
But, I don't know how to get that of ...
- 95
2
votes
1
answer
86
views
How to copy data between postgres schemas if there are objects?
I removed shop from database, that should not be removed. Fortunately I had backup and reverted this backup to schema public_backup.
Now I am trying to restore only this shop. I can't override my ...
- 209
0
votes
0
answers
92
views
postgresql sequence update
is there any difference in these two statements below to set the sequences after pg_restore?
CREATE TABLE public.your_table (
id serial4 NOT NULL, ---default to nextval('your_table_id_seq'::...
- 61
-2
votes
2
answers
97
views
Next 90 day review date (Teradata)
Teradata Related question… I have a table containing new starters along with a start date, every 90 days from the start date I need to conduct a review with the starter
How do I identify the next 90 ...
- 1
3
votes
2
answers
213
views
For each row create a linked row in another table with auto-generated ID
In Postgres, I have an existing table:
things
thing_id
thing_name
destination_id
10
thing 10
null
15
thing 15
null
For each row in that table, I want to add rows to two new related tables one of ...
- 167
0
votes
1
answer
48
views
EDB Replication Server, Sequences not updating
I'm using EDB Replication server to have 3 postgres 14 databases in sync (MMR Replication).
I'm using Table Trigger as changeset logging
It's working fine for the most part, but I have a problem with ...
0
votes
1
answer
457
views
Conditional default value based on sequence
There is a PG table example with two fk columns table_a_id, table_b_id and position::int column. It's guaranteed there will be only one column filled table_a_id or table_b_id.
CREATE TABLE table_a(
...
- 1
0
votes
2
answers
709
views
error to create sequence
trying to add auto_increment to an existing column
ALTER TABLE table_name ALTER COLUMN id_column SET DEFAULT nextval('table_id_column_seq');
try 1:
CREATE SEQUENCE table_id_column_seq AS integer ...
- 103
0
votes
0
answers
26
views
Postgres composite PK where one component is sequence different for each value of other field
Let's say I have a a_table with following fields:
id_a: bigint
id_b: int
some_third_value: text
I'd like to create a composite primary key on id_a and id_b so that id_a would be a sequence, but it ...
- 101
1
vote
1
answer
429
views
2 tables that use the same sequence to generate primary key in postgres
Could I use the same sequence to generate the primary keys on two separate tables for instance like this?
CREATE TABLE IF NOT EXISTS public."user_Registered"
(
id integer NOT NULL ...
- 247
1
vote
1
answer
1k
views
Generating an ID from primary key sequence without adding a row
I have the following two tables where I store users and their addresses. I have a problem relating to the order in which I receive and can save data from the user. At first the user will supply only ...
- 247
1
vote
1
answer
165
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 ...
- 25
0
votes
2
answers
513
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 ...
0
votes
1
answer
216
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 ...
- 105
-2
votes
1
answer
61
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 ...
- 165
0
votes
1
answer
133
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....
- 1,393
4
votes
2
answers
4k
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-...
- 109
2
votes
2
answers
248
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:
...
- 1,390
0
votes
1
answer
106
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 ...
2
votes
2
answers
5k
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 ...
- 371
4
votes
1
answer
406
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 ...
- 247
3
votes
2
answers
3k
views
Export Postgres sequences only
Is there a way using PG_DUMP or any other commands to export only sequences from an existing database?
- 231
0
votes
1
answer
90
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,417
2
votes
1
answer
3k
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?
- 167
7
votes
1
answer
7k
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, ...
- 305
0
votes
3
answers
5k
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 ...
- 591
0
votes
2
answers
550
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
1
vote
1
answer
287
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 ...
- 11
1
vote
1
answer
4k
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)?
- 133
0
votes
1
answer
680
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 ...
- 253
2
votes
3
answers
285
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 ...
- 121
1
vote
0
answers
34
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 ...
- 660
0
votes
1
answer
770
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 ...
0
votes
1
answer
762
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 ...
- 19
4
votes
1
answer
164
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 ...
- 53
6
votes
1
answer
1k
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 ...
- 229
-1
votes
0
answers
477
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....
- 21
0
votes
1
answer
1k
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) ...
- 2,108
1
vote
1
answer
2k
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 "(...
- 143
8
votes
2
answers
2k
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 ...
- 191
3
votes
1
answer
1k
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
1
answer
390
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 ...
- 11
5
votes
2
answers
2k
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 ...
- 197
-1
votes
2
answers
506
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 ...
- 221
2
votes
1
answer
108
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
1
answer
607
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 ...
- 145
12
votes
2
answers
28k
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 ...
- 307