An object that can generate integers.

learn more… | top users | synonyms

1
vote
1answer
41 views

Getting the next bigserial value in PostgreSQL [on hold]

Say I have a table with an id BIGSERIAL column. I just started a new session. I want to know what the id of the next row I insert will be. How can I tell this? I can't simply SELECT MAX(id) FROM ...
0
votes
2answers
53 views

PostgreSQL surrogate keys: use sequence/bigint or OID?

I'm looking at a need for surrogate keys in tables I'm designing on a PostgreSQL-derived DBMS (Citus). Would OIDs suffice? Is there a downside to using them instead of creating a bigint field and a ...
1
vote
3answers
102 views

How to get the next value of sequence?

Could somebody tell what is wrong with obvious query below: db2 => select next value for schema_name.sequence_name; As a result I'm getting: SQL0104N An unexpected token "END-OF-STATEMENT" ...
0
votes
1answer
36 views

Generate incrementing IDs without sequences

Suppose there's a multi-tenant application where users can create some kind of documents with basic structure like CREATE TABLE users (id SERIAL PRIMARY KEY, email TEXT); CREATE TABLE documents ( ...
0
votes
0answers
24 views

After recovering full backup with innobackupex, innodb system tablespace appears to be corrupt

I've a full backup of a database. From time to time, I recover those full backups on a recovery machine. This process has worked fine always, until today. The backup went OK, zero warnings, nor ...
1
vote
1answer
34 views

Chance of duplicate on single select max into statement

Against my will, I am told to use the following method to generate a new pk value. INSERT INTO SERVER_MONITERING(SM_SEQ, CPU_USE, MEMORY_BASE, MEMORY_USE, DISK_BASE, DISK_USE, ...
0
votes
1answer
16 views

MySQL InnoDB Error [duplicate]

I'm currently experiencing issues with my server MySQL databases as I keep receiving the following error: InnoDB: The log sequence in ibdata files is higher InnoDB: than the log sequence number in ...
0
votes
1answer
27 views

Unused Postgres sequence values

Observation: I have a serial index on a table in a PostgreSQL instance, and I execute a function that inserts a new record into that table. The function fails (reason not important), and the ...
8
votes
2answers
143 views

SQL Server 2008: sequence which restarts daily

I have to add a trigger which should update a column using the following format strings: <current_date>_<per_day_incremental_id>, e.g 2015-10-01_36. Ids must be incremental and gaps are ...
0
votes
0answers
161 views

How to include all sequences in pg_dump/pg_restore?

(Also posted to http://stackoverflow.com/q/32832486/398670) I have a PostgreSQL 9.4.4 database on my development server that contains test data that I want to dump and then restore to my production ...
3
votes
1answer
119 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 ...
0
votes
1answer
43 views

PostgreSQL: how to create both sequence and table simultaneously?

I need to create a table with a sequence associated to the gid column that would start from a given value, say 300. I'd like to know if there is a shorter way to achieve that: CREATE TABLE mytable ...
0
votes
1answer
33 views

I created a sequence but I can't alter the increment number after that [closed]

Create sequence CREATE SEQUENCE NEXT INCREMENT BY 2 START WITH 1000 MINVALUE 10; Alter sequence alter SEQUENCE NEXT INCREMENT BY 3 START WITH 1000 MINVALUE 10;
5
votes
1answer
256 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 ...
3
votes
1answer
105 views

Oracle sequences to MySQL

Migrating from Oracle to MySQL we've found some many incompatibility issues. We've some workarounds for many of them, however we still have one: The Sequences We have this table CREATE TABLE ...
3
votes
1answer
159 views

Insert a range of numbers from a call to sp_sequence_get_range

Given the following code: CREATE SEQUENCE dbo.NextTestId AS [bigint] START WITH 10 INCREMENT BY 2 NO CACHE GO DECLARE @variableNumberOfIdsNeeded INT = 7, -- This will change for each call ...
0
votes
0answers
122 views

Can I simulate a sequence in pre-2012 SQL Server, with separate identity column, in function style?

A database on SQL Server 2005/2008/R2 is currently using an identity column in a separate table as primary key source. The reason is that the application, with NHibernate as ORM, needs to know the PK ...
1
vote
2answers
96 views

How to populate another column with a sequence value generated for an identity column?

I have a table that has a column that is determined by a sequence using the identity column thing, so after a record is inserted (or during, I'm not sure how it works behind the scenes, only that it ...
2
votes
2answers
116 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
31 views

In Postgres, what is the relationship between a SEQUENCE and a SERIAL? [closed]

Postgres has the ability to define SERIAL datatypes and SEQUENCE but they seem to overlap in function. What are the main differences ?
3
votes
1answer
139 views

Postgresql import from SQL Server - how to auto-generate sequences

As the title states I'm switching from SQL Server to Postgresql via SQL dump. The tables and views have been migrated correctly, alas the IDENTITY columns have not been properly converted into ...
4
votes
1answer
301 views

How can I implement a sequence for each foreign key value?

In a PostgreSQL (9.3) database, I have a one-to-many relationship between Accounts and Customers, so within the Customers table, I have a globally-unique id for the primary key of each Customer, plus ...
1
vote
3answers
472 views

Copy table with separate sequences for serial columns

While creating a column management tool I came across the need to quickly replicate tables in PostgreSQL so I would not test new tools with non-test tables. To effectively test the new column tool ...
0
votes
2answers
86 views

sp_sequence_get_range equivalent in Oracle

Do we have sp_sequence_get_range equivalent in Oracle. I need to advance my sequence by some numbers always how can I achieve this.
0
votes
0answers
35 views

Explicitly acquire EXCLUSIVE LOCK on a SEQUENCE OBJECT

How can we explicitly acquire a exclusive lock on a Sequence object in SQL Server 2012? I am creating an application and need to lock a sequence object so no other transaction fetch data from it. ...
3
votes
1answer
1k 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 ...
0
votes
1answer
247 views

Large sequence cache in Oracle

There was a situation of 108,368,168 executions along with 7,373s elapsed time for "update seq$ set increment$=:........" statement while loading massive(i.e. populating million rows) data in the ...
1
vote
1answer
171 views

Next value for Sequence Function

Why does this give me the same value, four 1? USE [TSQL2012] GO IF OBJECT_ID('dbo.sqlsequence', 'SO') IS NOT NULL DROP SEQUENCE dbo.sqlsequence; GO CREATE SEQUENCE [dbo].[SQLSequence] AS INT ...
1
vote
1answer
68 views

MS SQL Server 2012 reusing numbers

I have a very high transaction database I have multiple sequences to unique generate numbers for different outlets. One of the outlets started receiving errors because i have a unique constraint on a ...
0
votes
3answers
497 views

Can i have one foreign key to both parent and it's childs in postgresql?

I want to have a foreign key to reference table and it's children. Here's how I tried and failed: CREATE TABLE parent ( id serial, CONSTRAINT parent_pk PRIMARY KEY(id) ); CREATE TABLE child ( ...
0
votes
1answer
197 views

Use serial ID for multiple columns and return it from INSERT

I am using Npgsql and I want to be able to do an insert, and if statement that will do an update and then return a value so when ExecuteScalar is called it reads that value. This was done fine in ...
0
votes
1answer
51 views

column value is not increment after 10

I tried below 2 codes.... showing same error 1.SELECT columnname FROM tablename ORDER BY columnname DESC LIMIT 1 SELECT MAX(columnname ) AS max FROM tablename I am trying to increment value of ...
3
votes
1answer
2k views

Permission for sequence in another schema

Postgres 9.3 Debian 7.0 I created a specific schema for a specific user and created a view in this schema for this user, so it's the only table he knows that exists. The problem is that this same ...
4
votes
1answer
6k 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 | ...
3
votes
2answers
3k views

Oracle ROWID, can we assume it is sequential in a insert-only scenario?

How safe is it to assume that ROWID is sequential or ascending when a table is only inserted to? and why? I had a situation where I had to verify whether new records were added to table which has no ...
5
votes
2answers
3k views

Adding a sequence number to existing rows

I have a table with an OrderSequence column. The schema looks roughly like this: id Name OrderSequence I have an array of ids ["1", "2", "3", "4"] from an application, and I want to set the ...
1
vote
2answers
587 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: - ...
2
votes
1answer
729 views

Explicitly granting permissions to update the sequence for a serial column necessary?

Recently I did create a table as a superuser including a serial id column, e.g., create table my_table ( id serial primary key, data integer ); As I wanted my non-superuser user to have ...
9
votes
1answer
908 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
2answers
683 views

How to get next value of Sequence dynamically in Oracle

I used to a SQL guy and trying my hands on Oracle front. I am getting dynamic values of sequence in a variable. I like to fetch next value for that sequence object in some output variable. How to ...
4
votes
3answers
329 views

Is there a good way to link tables and sequences?

I'm pretty sure you can't do this like you can with foreign keys but I'd like to document at the database level (even if it's just as a bit of free text somewhere) these relationships. Is there some ...
0
votes
1answer
66 views

Sequence and table with the same name?

Can we have a sequence and table with the same name? I am trying to create a sequence with the same name as an existing table, but I get this error: There is already an object named 'XYZ' in the ...
1
vote
2answers
80 views

Numbering a rows positional index relative to its siblings within a parent context

So, I have a table that contains numerous entries of text, but each one has a parent context that identifies the entries within a group, such as: +------+-------+------+ | PKID | GrpID | Body | ...
5
votes
1answer
16k views

Fixing table structure to avoid `Error: duplicate key value violates unique constraint`

I have a table which is created this way: -- -- Table: #__content -- CREATE TABLE "jos_content" ( "id" serial NOT NULL, "asset_id" bigint DEFAULT 0 NOT NULL, ... "xreference" varchar(50) ...
2
votes
2answers
805 views

Return ID from partitioned table in postgres?

When I insert a record I need to return the inserted id by RETURNING. The problem is that the table is partitioned and in partitioned table I can't use RETURNING. I run multiple queries at once so I'm ...
1
vote
1answer
1k views

error: must be owner of relation USER_ACCOUNT_ID_seq

I have granted priviledges to a user for testing in PostGreSql: GRANT USAGE ON SCHEMA "MySchema" TO "Tester"; GRANT ALL ON ALL TABLES IN SCHEMA "MySchema" TO "Tester"; GRANT ALL ON ALL SEQUENCES IN ...
2
votes
1answer
226 views

Postgresql behavior at end of sequence

As per subject when a sequence hits its max val what happens? What is the behavior? I have a big table with a sequence as a primary key and would like to know. Thanks.
0
votes
0answers
176 views

How do i replace DENSE_RANK() with a SEQUENCE object

I have the following select DENSE_RANK() OVER (ORDER BY A, B) AS C from ATable this select statement does the job but I have been told to replace it using a sql SEQUENCE object. Does anyone know ...
0
votes
1answer
288 views

Sequences Vs Count for auto-increment

Can some one please tell me the exact difference between the following statements , INSERT INTO table1 VALUES ((select count(column-name) from table-name2)+1); here i am using the count function to ...
2
votes
2answers
1k views

How to create a blank version of a PostgreSQL database

I've been using pgAdmin to create a database and test it. I'm at a point now where I need to install this db on different servers. I've been using pg_dump to do this and I've been able to muddle my ...