Skip to main content
Share Your Experience: Take the 2024 Developer Survey
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-...
JohnnyO's user avatar
  • 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 ...
Rick James's user avatar
  • 78.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) ); ...
Marco's user avatar
  • 3,710
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 ...
Solomon Rutzky's user avatar
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 ...
thatjeffsmith's user avatar
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 ...
jmoreno's user avatar
  • 1,065
6 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 ...
Shaneis's user avatar
  • 1,803
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 ...
danblack's user avatar
  • 7,789
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 ...
John Eisbrener's user avatar
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 ...
Evan Carroll's user avatar
  • 63.5k
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 ...
Rick James's user avatar
  • 78.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 ...
Manngo's user avatar
  • 2,839
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 ...
Erwin Brandstetter's user avatar
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 ...
a1ex07's user avatar
  • 9,010
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;...
Erwin Brandstetter's user avatar
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 ;
Melkij's user avatar
  • 3,189
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 '...
Rick James's user avatar
  • 78.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 ...
dbdemon's user avatar
  • 6,371
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 ...
Bill Karwin's user avatar
  • 15.1k
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'), ...
nbk's user avatar
  • 8,421
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 ...
Vérace's user avatar
  • 30.1k
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 ...
Evan Carroll's user avatar
  • 63.5k
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"). ...
ypercubeᵀᴹ's user avatar
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 ...
a1ex07's user avatar
  • 9,010
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 ...
Luan Huynh's user avatar
  • 1,972
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 ...
RHaberle's user avatar
  • 117
2 votes

auto-incrementing key in self-referencing table, performance

In indexes, DESC is ignored. (This will be rectified in version 8.0.) MySQL is happy to use an ASC index (that is, any index) but scan it in descending order. So DROP that redundant UNIQUE index. ...
Rick James's user avatar
  • 78.4k
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 ...
Rick James's user avatar
  • 78.4k

Only top scored, non community-wiki answers of a minimum length are eligible