An object that can generate integers.

learn more… | top users | synonyms

1
vote
1answer
58 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) ...
1
vote
2answers
102 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
0answers
61 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 ...
1
vote
1answer
47 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
66 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
65 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 ...
1
vote
2answers
99 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 ...
1
vote
2answers
85 views

Postgres sequence usage

I have an Postgres database with two users: dev and junit. Junit and dev objects are dropped and created regularly. They both have the same objects unshared for tables, however, sequences are shared, ...
3
votes
2answers
95 views

How do I create a table with a column that uses a sequence?

I have the following CREATE TABLE [MyTable] ( [ID] [bigint] PRIMARY KEY NOT NULL, [Title] [nvarchar](64) NOT NULL ) CREATE SEQUENCE MyTableID START WITH 1 INCREMENT BY 1 NO CACHE ...
0
votes
1answer
60 views

Insert a group of consistent item with foreign key but colliding with existing items

Is there a way to insert a group of items that are dependent but consistent between them with unique primary keys and foreign keys but that are colliding with items in database. For example, given a ...
0
votes
1answer
119 views

How can you have a combined primary key for one table?

I have exactly the same question as posted here. But in my case I do not use MySQL but Postgres. Can this be done with Postgres? All things indicate I can not use a combined index in Postgres, so ...
0
votes
2answers
115 views

When postgresql sequences get out of sync?

For the third time it has happened to me that a sequence has been set to an initial value (zero or one, not sure) while in the table, there are around 1500 records. When it happens, new rows cannot be ...
1
vote
1answer
149 views

Should I be concered by large SERIAL values?

I have a Django application that uses PostgreSQL to analyze data from tweets. The data set increases by thousands of records with each request. I am using the database primarily as a cache, so I had ...
1
vote
1answer
323 views

How to use db2look to get the ddl of sequence?

I was wondering how to use db2look to get the ddls of sequence. db2look -d my_db_name -z my_proc_schema - I tried this but it is not showing any results. Is there an option to directly mention the ...
1
vote
1answer
68 views

Polling for new records in a table

We have a table that is append+read only. We'd like to have multiple applications consume all new rows that show up in that database, in a cheap and easy way (without change tracking). The simple ...
0
votes
1answer
1k views

The InnoDB log sequence number is in the future

Our site was getting intermittent database errors and I asked my webhost to check what's happening. After some inspection they found that the database has few issues and tried repairing it. Finally I ...
-4
votes
2answers
157 views

What are the advantages of sequences in SQL Server 2012? [closed]

I upgraded my SQL Server 2008 R2 to SQL Server 2012, and I had noticed a new feature was added: Sequence. I don't know the reason of adding sequences when there is an identity property. Can someone ...
0
votes
2answers
81 views

How mysql process where condition

I have a query like Select * from tbl1 WHERE ( price > 10 AND qty > 200 AND condition=1 AND name like '%abc%') My question is how MySQL will process this? My concern is that I want ...
4
votes
2answers
530 views

Alternative to sequence and timestamp: uniquely ordering records in time

Oracle 11gR2 Exadata I'm required to uniquely identify when records are created in time. Sequence caching means I cannot use a sequence-based ID and batching inserts means that all records inserted ...
0
votes
1answer
125 views

How to store a sequence of events related to a transaction

I am using Sql Server 2008 R2. I have two tables that describe import processes and export processes. Import table has these columns with others that just have details about each type of import ...
3
votes
1answer
295 views

Is it possible to re-initialize sequence

How do I re-initialize the sequence value without drop and recreating them again For example, the current sequence value is 3, now I want to restart it with 1 again.
2
votes
1answer
1k views

Reset every Oracle sequence to the MAX value of its target table PK?

I finally got around to migrating from MySQL to Oracle and was pleasantly surprised just how well of a job the SQLDeveloper Migration tool did. It even took my AUTOINCREMENT fields and created ...
3
votes
2answers
939 views

Resetting a SQL Server 2012 sequence

I'm in the process of testing and populating a specific table that leverages the SEQUENCE object. In this process I'm testing populating the table with tens of thousands of insert lines (as I'm ...
2
votes
1answer
169 views

Generating an arithmetic sequence

I would like to generate a named table in a WITH clause which will contain this arithmetic progression: ╔══════════╗ ║ mynumber ║ ╠══════════╣ ║ 0 ║ ║ 30 ║ ║ 60 ║ ║ 90 ║ ║ ...
0
votes
1answer
271 views

MySQL non-sequential primary key of certain length

what's the best way to create a non-sequential primary key of a certain length in MySQL? I'm looking to use the primary key as a 'Pin' number which real actual human people can use to refer to the ...
3
votes
3answers
2k views

Using the same sequence twice in a PL/SQL statement

I wrote the snippet of PL/SQL today: declare first_id number; second_id number; begin insert into table (sort_nr, text_id, unit_id) values(..., table_seq.nextval, table_seq.nextval) returning ...
2
votes
1answer
266 views

Oracle sequence generation performance during persists of large data sets

In the context of an Oracle sequence, when persisting > 200 million records, is there a performance hit on the database if the sequence cache is the default value of 10000? That is to say does ...
1
vote
4answers
420 views

Limiting Generated Sequence Values in Multi-table Insert

In Oracle, given the following objects: create table a (x number ); create table b (val number ); create table c (val number ); create sequence my_seq; Suppose I populate table a as follows: ...
1
vote
1answer
426 views

Create unique numeric token on PostgreSQL

I have a database where I want to generate unique tokens of six numeric characters. These tokens should be generated each time an invoice number is inserted in the invoice_no column. This would mean ...
2
votes
4answers
185 views

What free database software support sequences

I have a requirement that a database must support sequences. I know oracle and PostgreSQL support it. Are there any other free database software that support database sequences? it would be great if ...
2
votes
2answers
2k views

Most efficient way to add a serial column to a huge table

What's the fastest way to add a BIGSERIAL column to a huge table (~3 Bil. rows, ~ 174Gb)? EDIT: I want the column to be incremented values for existing rows (NOT NULL). I didn't set a fillfactor ...
1
vote
1answer
2k views

Best practices for ensuring up to date sequences after table import

We perform weekly table moves between several schema using expdp and impdp. Some tables have sequences associated with the ID columns and I'd like to ensure that the sequences are always up to date on ...
11
votes
3answers
2k views

How could sequence.nextval be null in Oracle?

I have an Oracle sequence defined like so: CREATE SEQUENCE "DALLAS"."X_SEQ" MINVALUE 0 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 0 NOCACHE NOORDER NOCYCLE ; ...
2
votes
1answer
333 views

Using the special Dual table to read sequences

It there a performance impact when using the dual table to read a sequence' nextval over an direct insert? I'm currently optimizing the performance in a large IT system and what I've found is that ...
6
votes
1answer
619 views

Guarantee SQL Server Identity Columns that two consecutive values differ by constant increments?

I posed a question about Denali sequences here and another question about emulation of identity columns in Oracle Global Tables here. I always thought that I could trust that identity values ...
15
votes
2answers
21k 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 ...
22
votes
2answers
1k views

Why are Denali sequences supposed to perform better than identity columns?

In his answer to sql2005 whats better: identity columns or generated uinique id values? mrdenny mentions, says When SQL Denali comes out it will support sequences which will be more efficient ...