110 questions
1
vote
2
answers
749
views
Spring Boot with Oracle Sequence - 2 different applications get colliding SEQUENCE.NEXTVAL causing ORA-0001 unique constraint violation on primary key
I have 2 applications (one is SpringBoot app and another is a C++ app) calling the same Oracle sequence SEQUENCE_A responsible for generating PK on the same table TABLE-A.
The sequence DDL is as:
...
- 10.8k
1
vote
0
answers
796
views
Hibernate-sequence generates duplicate keys when using hibernate envers
I am using hibernate envers 5.6.9.final with spring boot 2.7.2.
my problem is that hibernate_sequence sometimes generates duplicates value when it needs to insert a new record in revisionInfo tabel. ...
- 321
1
vote
0
answers
29
views
Inconsistent Hibernate Behavior while using GenerationType.SEQUENCE
I see inconsistent hibernate behavior when using sequence to generate id, I have simple project with one entity Fruit and I'm using spring.jpa.hibernate.ddl-auto=create-drop so hibernate is launching ...
- 11
1
vote
2
answers
1k
views
How to auto increment one column of a postgres composite primary key?
I am making a simple note app, and I want to create a table to track a note's edit history and save them with an incrementing version as a composite key with the note's id. So for example:
note_id | ...
- 124
0
votes
3
answers
313
views
Get the next 10 values of the SEQUENCE at once
I want to get the next 10 values of the SEQUENCE at once.
I can get those individually by calling
SELECT setval('myseq', ...); -- Next nextval will return 43
10 times back to back.
On ...
- 6,634
3
votes
1
answer
633
views
PostgreSQL - Common autoincrement with inherited tables
I'm currently trying the inheritance system with PostgreSQL but I have a problem with the auto-increment index in my child tables.
I have three tables: "Currency", "Crypto" and &...
- 989
0
votes
0
answers
129
views
Sequence in DB Postgresql
I have a Java system that uses Sequence in PK in database (postgreSQL and PGadmin)
This sequence should take the current month (with two digits) followed by the current year.
It turns out that a ...
- 3
0
votes
1
answer
114
views
Hibernate Sequence Logic
I am having a constraint violation issue with Hibernate and the constraint is related primary key. I debugged a bit and see that the before insertion, sequence value selection as below
select ...
- 2,585
0
votes
1
answer
837
views
Why SEQUENCE is used in SQL? [duplicate]
I am brushing up my knowledge about SQL and I am learning more and more. However, I am not understanding the purpose of the Sequence we use. I am using SQL Server.
- 27
0
votes
1
answer
2k
views
How to fix NEXTVAL returning null in insert query but returns correct value when executed alone in PostgreSQL?
When I execute the following insert query:
insert into FEED_STAGING_GOOD_TRADE (JOBEXECUTIONID, STAGINGID, ROWNUMBER, system, traderef, mtmvaluation, mtmvaluationccy, prcmtmquality, prcdate)
values (...
- 989
0
votes
1
answer
1k
views
PostgreSQL ALTER SEQUENCE with SELECT clause
I am trying to alter database sequence and restart it with value returned by complex SELECT statement. This is a simplified example that I prepared to replicate the issue:
ALTER SEQUENCE
abc....
- 2,329
3
votes
3
answers
12k
views
How to create a PostgreSQL Table id Field and Define a Custom Sequence for It?
My table is defined as:
CREATE TABLE accounts IF NOT EXISTS (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL
);
At some circumstances, I want to fetch next val of id before inserting data ...
- 75.9k
0
votes
1
answer
429
views
Consuming sequence inside a embeddedId in springboot
I have the next issue -> I have a table on my db with a composite id...
Supose (Id1,Id2,Id3,Id4), the Id4 is generated by a sequence on the db...
My question is, in spring boot, I generate the ...
0
votes
1
answer
159
views
PostgreSQL column to keep track of number of appearances of a certain kind [duplicate]
I have to make a new column inside of an already existing database that keeps track of the number of occurences of a certain kind. Here's an example to be more clear:
id | eqid | etcd | this would be ...
- 103
0
votes
1
answer
171
views
Postgres sequence that resets once the id is different
I am trying to make a Postgres sequence that will reset once the id of the item it is linked to changes, e.g:
ID SEQUENCE_VALUE
1 1
2 1
1 2
1 3
...
- 103
1
vote
1
answer
1k
views
Is it OK to add @Id to an entity which mapped to a table without Primary key column in Spring boot Jpa?
I've started working on a legacy oracle database and using Spring boot Jpa trying to insert a new row in a table (CHANNELMGR_REQUEST) without Identity:
This table has a Numeric column (CM_ISN) and ...
- 3,313
0
votes
2
answers
1k
views
Spring Boot entity with Pk using Oracle DB Sequence in a trigger
I need a help on persisting an entity in a Oracle DB table that uses trigger and sequence for PK.
By now, I tried these from other stackoverflow questions:
@Id
@GeneratedValue(generator="...
- 181
7
votes
1
answer
3k
views
Sequence exists but I can't find it in information_schema.sequences (PostgreSQL)
I have created a sequence (let's call it my_seq) in a schema (let's call it my_schema) of my PostgreSQL (version 13) database. I am sure the sequence exists because I can find it in the result set of ...
- 81
0
votes
1
answer
523
views
Sequence id is not properly inserted
I have created a sequence but its not inserting ids in sequence order.
For Ex:
First I have created one set of record seq number generated as 1, 2, 3, 4
Again I have created another set of records ...
- 223
1
vote
2
answers
3k
views
How to reset the auto generated primary key in PostgreSQL
My class for the table topics is as below. The primary key is autogenerated serial key. While testing, I deleted rows from the table and was trying to re-insert them again. The UUID is not getting ...
0
votes
2
answers
661
views
2 Applications using different hibernate versions but same oracle database throwing unique constraint error
There are 2 applications : one is using Spring boot - 1.5.18.Release version, which has hibernate version as 5.0.12.Final
:https://search.maven.org/artifact/org.springframework.boot/spring-boot-...
- 147
0
votes
1
answer
338
views
SERIAL PRIMARY KEY keeps counting when the csv importing file fails in pgAdmin
I have table with SERIAL PRIMARY KEY and when importing csv file with some data and it fails for any reason, when importing successes the PRIMARY KEY instead of starting from zero (in case the table ...
- 305
0
votes
0
answers
27
views
Sequence is incremented even if no INSERT occurs [duplicate]
I've a table with following structure:
CREATE TABLE web.cabinet_account_section (
id serial NOT NULL,
section_id text NULL,
account_id int4 NULL,
cabinet_param_id int4 NULL,
...
- 149
0
votes
0
answers
156
views
How to declare a function with a select statement to reset all auto_increment values to max id of the tables in postgresql
I have a Postgres database with many tables. I want to declare a function that reset all auto_increment values (next_value) of all the tables to max(id) of the table. For example, if max(id) of ...
- 2,514
0
votes
1
answer
98
views
Postgres tech role for sequence only
There is role sequence_owner. This is special tech role for working with sequence only.
create role sequence_owner;
GRANT sequence_owner TO administrator;
GRANT USAGE, SELECT, UPDATE ON ALL SEQUENCES ...
- 1,353
0
votes
1
answer
63
views
Postgresql sequencer not found
I'm creating an item record number generator. The goal is to have a table to house all record number/sequencers for a variety of different types. For example, for a "Part" you may want a ...
1
vote
1
answer
164
views
Can't figure out what datatype is incorrect in my SQL table
I am trying to create a SQL table, but I keep getting this error.
Error report -
ORA-00902: invalid datatype
00902. 00000 - "invalid datatype"
Here is my code.
CREATE TABLE viewers
(
...
- 109
4
votes
2
answers
2k
views
Should I lock a PostgreSQL table when invoking setval for sequence with the max ID function?
I have the following SQL script which sets the sequence value corresponding to max value of the ID column:
SELECT SETVAL('mytable_id_seq', COALESCE(MAX(id), 1)) FROM mytable;
Should I lock 'mytable' ...
- 162
1
vote
3
answers
4k
views
How to increment id without auto increment?
I have a table with id column as a number which have meanings. Different types of accounts start from different ranges. E.g Organisation 10000 <-> 100000, users 1000000 <-> 1kk. How can i ...
10
votes
4
answers
7k
views
How to bulk update sequence ID postgreSQL for all tables
I imported the Postgres SQL file to my server using TablePlus(SQL client), but after I insert new row I got error like this:
SQLSTATE[23505]: Unique violation: 7 ERROR: duplicate key value violates ...
- 8,358
5
votes
3
answers
7k
views
Reset Postgres auto-increment value to 0
I'm having a table where I sometimes enter data in inline editors where I manually add the id value which is usually auto-incremented.
In a case where id 4 was auto generated using scripts and then ...
- 4,922
0
votes
1
answer
1k
views
How to start my Serial key from 1 in PostgreSQL [duplicate]
How to start my id column from 1 ?
My tabel is :
id | origin | destination | duration
----+-------------+-------------+----------
6 | Paris | New York | 540
7 | Tokyo | ...
0
votes
1
answer
170
views
Share sequence from 2 user in Postgres
In database server Postgres 9.x or Postgres 10.x can I share the same sequence from 2 user?
For example in the same database dbTest there are 2 schemas
userA is schema owner schemaA with a sequenceA
...
- 65
1
vote
1
answer
115
views
PostgreSQL Squence Not Working When Updating Column Details
I have created a function to update a column in a postgresSQL table using Sequence nextval() function.Function body is as follows
BEGIN
EXECUTE 'CREATE SEQUENCE '|| sequence_name || ' START 1';
...
- 716
0
votes
0
answers
281
views
HibernateException: Missing sequence or table and SQLIntegrityConstraintViolationException: ORA-01400: cannot insert NULL into
I am fairly new to hibernate and this is very strange - I am trying to run a Spring-Hibernate rest application on tomcat using eclipse.
I have an entity defined as below -
@Entity
@Table(name = "...
- 1,009
0
votes
4
answers
129
views
Select sequence number not allowed here
I´ve a problem than i can´t resolve a select with a sequence, here´s my query
SELECT SEQ_ARRIENDO.nextval,
TO_CHAR(SYSDATE,'YYYY') ANNO_PROCESO,
cam.nro_patente,
( SELECT ...
1
vote
0
answers
34
views
Serial id next value not getting sequential after failed insert attempt [duplicate]
I have a id field where id is serial and in the same table i have unique constraints. The problem here is when an unique constraint fails if the current ID value is 5 then after failing the next ID ...
- 903
0
votes
1
answer
293
views
Why does Azure SQL Database and MS SQL Server documentation say sequences are type sysname when they can be configured as anything?
I was looking at the examples and types while seeing how to use the SEQUENCE command in T-SQL (Azure SQL Database, SQL Server, etc.), but when it lists the types SEQUENCE can be configured as (INT, ...
- 80
1
vote
1
answer
2k
views
What is the equivalent for NO ORDER of oracle sequence in postgresql?
I have a query
CREATE SEQUENCE "SEQ_ID"
MINVALUE 1
MAXVALUE 99999
INCREMENT BY 1 START WITH 121
CACHE 20
NOORDER
NOCYCLE;
This one is of oracle commands.
Now, I ...
- 159
2
votes
2
answers
2k
views
Custom progressive sequence (per year) with a column as prefix
I need to create a custom sequence based on a specific column, added as a prefix. I know it is possible to customize the sequence as well as the nextval, but I'm not sure if it is possible to use the ...
0
votes
2
answers
93
views
I try to insert query alphanumeric sequence in table but it is not working
I am trying to insert an alphanumeric sequence in Oracle but it is not working.
create sequence LIB start with 1 increment by 1;
select 'LIBR'||to_char(seq_no.nextval,'FM0000099') from dual;
...
1
vote
2
answers
342
views
Oracle - does a column have a sequence attached to it?
Kind of a general question here but is there an easy way to determine, in Oracle database, if a field has a sequence number attached to it? This seems like it should be obvious, but I'm missing it.
...
- 1,350
0
votes
3
answers
843
views
Oracle - How to rollback sequences inside multiple inserts
I have a script (that can fail) with multiple inserts that include nextval as
insert into table a (id, value) (id_seq.nextval, 'value');
...
If script fails and I rollback the inserts, the sequence ...
- 59.3k
1
vote
3
answers
2k
views
Generating incremental numbers based on a different column
I have got a composite primary key in a table in PostgreSQL (I am using pgAdmin4)
Let's call the the two primary keys productno and version.
version represents the version of productno.
So if I ...
user11962606
0
votes
2
answers
452
views
How to use sub-queries correctly inside a Postgresql query
I'm having troubles resetting the sequences as automatically as possible.
I'm trying to use the next query from phpPgAdmin:
SELECT SETVAL('course_subjects_seq', (SELECT MAX(subject_id) FROM ...
- 57
0
votes
1
answer
1k
views
How to update/increment an Oracle sequence by ten million? [duplicate]
I have a (Oracle) sequence that I use to assign an order number to new orders being inserted into a database table. It started at 1, and increments by 1 for each new order.
I have a new requirement ...
- 612
0
votes
1
answer
111
views
Cannot insert a number using SQL - whole column is removed / number is null
I'm trying to insert a Number as an ID in a column in my database by a loop which sets the new ID by a Sequence. When I try to insert it, the row hasn't been created or the field where my number ...
- 53
1
vote
2
answers
4k
views
postgreSQL: primary key doesn't start on 1
I made a primary key called t_id in the table of DB by using the query t_id SERIAL PRIMARY KEY.
At the very first time, it started well on 1. Then I deleted all the data and from then, it starts on 2 ...
user11492726
0
votes
0
answers
72
views
Update unique id (start with 1)against the foreign key and unique id will again start with 1 the next foreign key
I need to update product_details table with unique ID(CHARGE_NUMBER) againt each batch_ID and for the next batch ID unique ID again will start with 1
I have write a code using sequence.
update ...
- 17
0
votes
1
answer
622
views
How to change next default value for PostgreSQL sequence? [duplicate]
I have a table where the ID sequence goes like 01,02 and so on (I have a few pre-inserted values). When I try to insert a new row there (not specifying the ID explicitly, but rather values for other ...
- 97