Skip to main content

Questions tagged [sequence]

An object that can generate unique integers.

Filter by
Sorted by
Tagged with
1 vote
1 answer
51 views

Is it possible to change the sequence backing an identity column?

After a machine malfunction and a hurried transfer of data onto another machine, we have somehow ended up with some brand new sequences replacing old sequences as the backing for identity columns (and ...
John Denniston's user avatar
0 votes
2 answers
71 views

Trying to renumber a field in order grouping by another

So I have a table that has a column labeled 'sequence', it is not in fact a sequence data type, just a numeric field that translates in the object model to show items in a certain order. In a handful ...
Jonathan Beck's user avatar
0 votes
2 answers
38 views

(PostgreSQL) Insert a tuple containing specific value in a column linked to a sequence

I started writing this post as a question but, while reproducing the error, I found the solution in the PostgreSQL documentation. So I'll share the solution here for the community. A PostgreSQL 14 ...
ValerioOliveira's user avatar
0 votes
1 answer
44 views

Any way to debug an Oracle Sequence?

Somewhere in a large project a sequence is being incremented one too many times. Is there some way to add a trigger or watch to the sequence when .NEXTVAL is invoked so we can get a call stack and see ...
Yimin Rong's user avatar
0 votes
1 answer
14 views

PostgreSQL Tables Without Unique Constrtaint On Sequence

I use PostgreSQL. Is there a query to get a list of all tables that do not have the unique constraint on the sequence column? If it helps forming a query I use "reference" for my sequence ...
Ron Piggott's user avatar
1 vote
1 answer
249 views

Query for repeating sequences in SQLite

I have an SQLite DB, let's assume the id can be considered as the order of rows, I want to find repeating sequences in the table according to certain criteria. consider this example (http://sqlfiddle....
yossico's user avatar
  • 167
1 vote
2 answers
506 views

PosgtreSQL — Is it possible to specify a sequence name in a GENERATED BY DEFAULT AS IDENTITY column?

When using PHP’s lastInsertId() method with PostgreSQL, it’s necessary to know the name of the sequence. If I defined the column using GENERATED BY DEFAULT AS IDENTITY, I know I can get the sequence ...
Manngo's user avatar
  • 2,849
0 votes
1 answer
79 views

How to find missing number from different ranges

I have a table with two columns, one containing name and other numbers. For each name there is range of numbers and there are around 250 names in there. Range of every name is different. It may be 1 ...
Indresh Mangal's user avatar
0 votes
1 answer
117 views

How to increase sequence by condition in Oracle Database 19.x?

We have the table as below. CREATE TABLE "STYLES" ( "STYLE_CODE" CHAR(8) GENERATED ALWAYS AS ("COLOR"||"FEATURE"||"SIZE"||"YEAR")...
fackman's user avatar
0 votes
1 answer
70 views

PGPOOL Load Balancing: Create Sequence is going to replica

I created a pgpool instance which can connect to one master database(primary for write) and one read replica. I have enabled below parameter: disable_load_balance_on_write='dml_adaptive'; Now my ...
Sajith P Shetty's user avatar
0 votes
1 answer
181 views

How to use setval into a function?

I created a trigger function to set the value of a sequence in a remote database table, using dblink and setval. CREATE OR REPLACE FUNCTION my_function () RETURNS TRIGGER AS $$ DECLARE ... BEGIN ...
Sébastien Clément's user avatar
0 votes
1 answer
448 views

Find all sequences and other schema objects owned not by certain user

How can I find all sequences (and more widely - all objects in given schema), which are owned not by certain user, on PostgreSQL (10-15)? I found such query: SELECT relname, relacl FROM pg_class pgc ...
lospejos's user avatar
  • 109
1 vote
2 answers
1k views

Insert values and add values to foreign key

I have two tables: CREATE TABLE est ( est_id serial PRIMARY KEY , est_nom varchar(70) ); CREATE TABLE bm ( id serial PRIMARY KEY , est_nom int , nom varchar(70) , CONSTRAINT FK_bm_est FOREIGN KEY(...
David Palacios's user avatar
0 votes
1 answer
653 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 ...
sam's user avatar
  • 11
0 votes
2 answers
280 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 ...
Viz Krishna's user avatar
-3 votes
2 answers
136 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 ...
George's user avatar
  • 1
0 votes
1 answer
353 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 ...
Viz Krishna's user avatar
2 votes
1 answer
405 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 ...
Daniel's user avatar
  • 209
-2 votes
2 answers
346 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 ...
Spindo82's user avatar
3 votes
2 answers
1k 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 ...
Robert Elliot's user avatar
0 votes
1 answer
99 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 ...
Christos Arapidis's user avatar
1 vote
1 answer
2k 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( ...
mzsrn's user avatar
  • 11
0 votes
2 answers
2k 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 ...
Richard's user avatar
  • 103
0 votes
0 answers
81 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 ...
Tarmo's user avatar
  • 101
1 vote
1 answer
1k 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 ...
sev's user avatar
  • 247
1 vote
1 answer
4k 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 ...
sev's user avatar
  • 247
1 vote
2 answers
345 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 ...
HeatZync's user avatar
0 votes
3 answers
1k 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 ...
Harshit Tekriwal's user avatar
0 votes
1 answer
333 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 ...
ACC's user avatar
  • 105
-2 votes
1 answer
157 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 ...
Ron Piggott's user avatar
0 votes
1 answer
214 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....
Artashes  Khachatryan's user avatar
9 votes
2 answers
8k views

Why is the serial primary key incrementing despite a using "On Conflict Do Nothing?

I have the below table in PostgreSQL 13: table name: newtable field type ----- ---- Seq bigserial code varchar Seq is the primary key (auto-increment) Code is ...
Don2's user avatar
  • 469
2 votes
2 answers
409 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: ...
Pantea's user avatar
  • 1,432
0 votes
1 answer
155 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 ...
Muhammad Farooq's user avatar
3 votes
2 answers
10k 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 ...
user1068636's user avatar
4 votes
1 answer
697 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 ...
sev's user avatar
  • 247
3 votes
2 answers
6k views

Export Postgres sequences only

Is there a way using PG_DUMP or any other commands to export only sequences from an existing database?
Benjamin's user avatar
  • 231
0 votes
1 answer
227 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 ...
User1974's user avatar
  • 1,477
2 votes
1 answer
6k 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?
Miantian's user avatar
  • 167
13 votes
1 answer
22k 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, ...
jgrocha's user avatar
  • 365
0 votes
3 answers
6k 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 ...
Dolphin's user avatar
  • 785
0 votes
2 answers
1k 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 ...
mfridman's user avatar
1 vote
1 answer
626 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 ...
Valachor's user avatar
2 votes
1 answer
12k views

How can I identify sequences used by tables?

Is there a way to query an Oracle database that would return: a listing of all the tables in the database which sequence it is using (if any)
gtludwig's user avatar
  • 143
0 votes
1 answer
2k 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 ...
Lohit Gupta's user avatar
2 votes
3 answers
333 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 ...
Simon Notley's user avatar
1 vote
0 answers
36 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 ...
Paul Draper's user avatar
0 votes
1 answer
1k 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 ...
user2894356's user avatar
0 votes
1 answer
1k 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 ...
anaigini's user avatar
4 votes
1 answer
325 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 ...
bn25's user avatar
  • 53