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 ...
- 76.9k
18
votes
How do I specify that a column should be auto-incremented in pgAdmin?
If you want to do this in PGAdmin, it is much easier than using the command line. It seems in PostgreSQL, to add a auto increment to a column, we first need to create a auto increment sequence and add ...
- 301
17
votes
Accepted
Add autoincrement to existing PK
The way I understand your question is that you have an existing table with a column that has up until now been populated with manual values, and now you want to (1) make this column an IDENTITY column,...
- 9,095
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,700
9
votes
Create a "INTO" table with primary key
Some alternatives to adding the auto-increment column via the IDENTITY() function as suggested by @Shaneis are:
Create the table explicitly using CREATE TABLE instead of using SELECT INTO. I much ...
- 68.3k
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,002
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,053
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 ...
- 7,649
5
votes
Add autoincrement to existing PK
Using UPDATE, DELETE or INSERT to move data can take quite a lot of time and use resources (IO) on both data and log files/disks. It is possible to avoid filling the transaction log with potentially ...
- 10.1k
5
votes
Accepted
Create a "INTO" table with primary key
Sounds like you are looking for the IDENTITY() function:
Is used only in a SELECT statement with an INTO table clause to insert
an identity column into a new table.
USE [MYDB]
GO
SET ANSI_NULLS ...
- 1,793
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,342
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 ...
- 76.9k
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,759
4
votes
Add autoincrement to existing PK
If you want to start with a new identity value you need to reseed your identity. Have a look at the documentation for CHECKIDENT
DBCC CHECKIDENT (yourtable, reseed, starting point)
- 15.6k
4
votes
Accepted
Trick for AUTO_INCREMENT on composite keys in InnoDB
My solution is in migrating from myisam to innodb. It goes something like:
BEGIN;
SELECT @id := IFNULL(MAX(id),0) + 1 FROM foo WHERE other = 123 FOR UPDATE;
INSERT INTO foo
(other, id, ......
- 76.9k
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 ...
- 172k
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 ...
- 62.1k
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 ...
- 8,970
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;...
- 172k
4
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 ;
- 2,774
3
votes
using IDENTITY column with increment -1
This sounds like a variation on data partitioning by key. An alternate method is to use the seed and increment so that each data center generates values that interleave but do not collide. Try ...
- 31
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 '...
- 76.9k
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,261
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 ...
- 13.9k
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'),
...
- 7,917
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 ...
- 29.5k
2
votes
autoincrement column issue in MYSQL
The actual thing is the variable "innodb_autoinc_lock_mode" which control the behavior for auto_inc lock during generation of auto generated numbers. It can be set to three values which are 0 ,1 or 2. ...
- 1,440
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
...
- 62.1k
Only top scored, non community-wiki answers of a minimum length are eligible