23
votes
How do I specify that a column should be auto-incremented in pgAdmin?
This is pretty easy in pgAdmin 4.
First, add a column to your table, then click the little edit icon:
Then go to Constraints and select the Identity type:
Save your table and the column will auto-...
- 331
19
votes
Accepted
Identity column re-seed: when it is necessary?
That is, when table row is deleted, it's PK must be reused in subsequent inserts.
What universe is your lecturer from??
That is grossly inefficient. If you try to do that, you will cut your ...
- 79.4k
11
votes
Accepted
Creating 7 digit codes in database for every new insertion
If it always needs to start with 51 then you can use a sequence (or identity) that starts at 510,000,001.
CREATE TABLE #Test (
ID BIGINT IDENTITY(510000001, 1),
SomeColumn VARCHAR(10)
);
...
- 3,720
9
votes
Is it possible to create an alias/shortcut/makro/function for a collection of keywords?
You can tell the tool, in this case SQL Developer, to do it for you.
I talk about how to set this up here
https://www.thatjeffsmith.com/archive/2014/02/auto-replace-in-oracle-sql-developer/
But ...
- 1,012
9
votes
Identity column re-seed: when it is necessary?
Reusing an identity value, should in general be discouraged. Either the value is used entirely internally, in which case it’s actual value is immaterial, or it is also used externally in which case ...
- 1,075
6
votes
Identity column re-seed: when it is necessary?
The reuse of PK id values has problems and generally should be avoided.
First, the implementation of auto_increment columns doesn't provide the guarantee of being gapless. Indeed gaps will occur if ...
- 8,009
5
votes
Accepted
Custom Sequences with SQL Server
Why don't you just use an actual SEQUENCE which was introduced to SQL Server with 2012?
The following approach overloads the BIGINT value where the leading 10 digits represent the server IP address ...
- 9,487
5
votes
How I can remove autoincrement from a Primary key in postgresql?
Just remove the default value on column and the corresponding sequence
alter table values alter column id drop default ;
drop sequence values_id_seq ;
- 3,412
4
votes
Accepted
Best practices for generating unique multi-column keys for weak entities?
1. is the least error-prone, simplest and fastest.
Trigger solutions like in 2. or 3. are subject to subtle race conditions under concurrent write access.
Make item_number a serial column and also ...
- 182k
4
votes
Accepted
Is there any performance reason to append an unused AUTO_INCREMENT column in MySQL 5.6?
Yes and no.
(The following applies to all versions, but only to ENGINE=InnoDB.)
An InnoDB table always has a PRIMARY KEY:
You could specify one; else
A UNIQUE key with non-null column(s); else
A ...
- 79.4k
4
votes
Accepted
Creating Auto Number field with preceding number of 0's?
Without getting into a technical argument, once you start getting fussy about the display of a number, it is no longer just a number. (Mathematically, we refer to a number as its value, while its ...
- 2,919
4
votes
Accepted
Is it safe to rely on auto increments even after restoring data?
A quick overview:
PostgreSQL does not have AUTO INCREMENTS. There is no special type.
In order to set this up PostgreSQL uses the standard IDENTITY COLUMNS which associates a SEQUENCE with the int ...
- 64.6k
4
votes
Accepted
How to make an existing column autoincrement in Oracle
If you insist on using IDENTITY, it is not be possible - you cannot modify non-identity column to identity. However, Oracle lets you use sequence.nextval as default, so you may get similar ...
- 9,030
4
votes
Accepted
How to add a serial ID to a table?
Add the id column like this, and the column will be filled with serial numbers automatically, and indexed as PK:
ALTER TABLE public.ecdict ADD COLUMN id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY;...
- 182k
3
votes
GUID vs Auto Increment Insert performance
What you are missing is the setting of innodb_buffer_pool_size (assuming you are using InnoDB), and how it compares to the size of the index on id. The "more data" needed:
SHOW VARIABLES LIKE '...
- 79.4k
3
votes
Accepted
MariaDB is AutoIncrementing on update
The following assumes that you feel the gaps in the id sequence is a problem. This usually isn't a problem, unless these gaps become enormous and there is a risk that at some point you will exceed the ...
- 6,479
3
votes
Accepted
MySQL InnoDB Reset Existing Auto-Increment Records
TRUNCATE TABLE tbl;
If that does not work,
ALTER TABLE tbl AUTO_INCREMENT=1;
But you should not depend on the actual values of AUTO_INCREMENT. There are many things that can make it non-...
- 79.4k
3
votes
Accepted
Export and import a table without a specific column
Another solution is to use LOAD DATA [LOCAL] INFILE, but override the columns list so the auto-increment value is inserted into a dummy variable:
Here's a demo:
I have a text file I call c.csv:
1 ...
- 16k
3
votes
Accepted
Apply nth occurrence number on each distinct value in a column
In mysql 5.5
you can use user defined variables for that
CREATE TABLE tab1
(`SOURCE_VAL` varchar(1))
;
INSERT INTO tab1
(`SOURCE_VAL`)
VALUES
('a'),
('a'),
('b'),
('c'),
...
- 8,668
3
votes
Accepted
PostgreSQL: INSERT in a sequence and increment all values above the INSERT (make "room" for the INSERT)
First, I will answer the question as asked, then suggest a couple of improvements as I see it and then suggest that you completely revise your schema! All the code below is available on the fiddle ...
- 30.6k
3
votes
Can a new transaction claim an older sequence id?
Is it expected that this can happen. A newer transaction claiming a lower seq value?
Yes.
The formulation "claiming a lower seq value" is fuzzy. You seem to be thinking "writing a ...
- 182k
2
votes
Accepted
MySql autoincrement counter resetting after server restart
MySQL fix
After 14.5 years this is fixed in MySQL version 8.0
[6 Oct 16:40] Daniel Price Posted by developer: Fixed in 8.0.0 by WL#6204.
WL#6204: InnoDB persistent max value for autoinc columns
...
- 64.6k
2
votes
Why do database host services increment id by > 1 unit?
A case for this "step" to be greater than 1, is when we have a distributed database into more than one servers and we want to allow inserting into all (some DBMS call this setup "multi-master").
...
- 98.6k
2
votes
Why do database host services increment id by > 1 unit?
The only reason I can think of is "reserving" some data slots for the future usage. That would work if a table is index-organized ( or clustered in Microsoft terminology). For instance, if we have ...
- 9,030
2
votes
MariaDB is Forgetting Previous Auto Increment IDs
Only a restart can explain this (as far as I know). So, "if too much time passes before an insert occurs," as you say, and a restart happens at this time, there are no rows in the table and the engine ...
2
votes
Accepted
How to add a new column with auto increment ,increment factor, min-value and max value in psql?
Here, you can use sequence to increase app_id value.
Please noted if the sequence reach to its maxvalue, you will face this error ERROR: nextval: reached maximum value of sequence .
For example
...
- 2,002
2
votes
Accepted
Inserting Missing day of data into table - Need to increment primary key by 618 for each key
Since you're changing the identity of the row, can we assume that it's not used as a foreign key of another table? If it's only purpose is to uniquely identify the row of the table it's on, then don't ...
- 117
2
votes
Help me understand and resolve MySQL deadlock - "lock mode AUTO-INC waiting"
The schema for n0_table looks like it is straight from Wordpress? It has multiple inefficiencies. Revamp it to make queries faster and either eliminate or decrease the frequency of the original ...
- 79.4k
2
votes
Accepted
MySQL - Set trigger BEFORE INSERT for new ID is safe?
Do not make auto_increment any column you want to manipulate explicitly. That can confuse an engine and cause serious problems. If no column you have used for primary key are auto_increment you can do ...
- 4,581
Only top scored, non community-wiki answers of a minimum length are eligible