With Identity 1 1 Start From 1 Again
PostgreSQL - SERIAL - Generate IDs (Identity, Auto-increment)
SERIAL information blazon allows you to automatically generate unique integer numbers (IDs, identity, auto-increase, sequence) for a column.
Quick Example:
-- Define a tabular array with Serial column (id starts at 1) CREATE TABLE teams ( id SERIAL UNIQUE , proper noun VARCHAR (90) ); -- Insert a row, ID volition be automatically generated INSERT INTO teams (name) VALUES ( 'Tottenham Hotspur' ); -- Retrieve generated ID (just 1 of the possible options) SELECT LASTVAL( ); -- Returns: 1
Overview
PostgreSQL Serial data type:
| Showtime Value |  | Ever one | Modify SEQUENCE RESTART WITH to change | |
| Increase | | E'er 1 | ALTER SEQUENCE Increase Past to change | |
| How to Generate IDs | Omit the Serial column in INSERT, or specify DEFAULT keyword | |||
| Explicit ID Insert |  | |||
| Multiple Series per Table | | |||
| Constraints | Non NULL | Added automatically | ||
| Main or unique fundamental | Not required and not added automatically | |||
| Remember Last ID | LASTVAL() | Returns the last ID inserted in the current session | ||
| CURRVAL('seq_name') | Returns the current ID for the specified sequence | |||
| INSERT … RETURNING serialcol returns ID immediately after INSERT statement | ||||
| Gaps | If a value is explicitly inserted, this has no effect on sequence generator | |||
| Restart (Reset) | ALTER SEQUENCE tablename_serialcol_seq RESTART WITH new_current_id; | |||
| Alternatives | BIGSERIAL | 64-bit ID numbers | ||
| Using a sequence and DEFAULT NEXTVAL('seq_name') | ||||
| OID organisation cavalcade | ||||
| Synonym | SERIAL4 | |||
Version: PostgreSQL ix.1
PostgreSQL Serial Data Type Details
When you define a Series column, PostgreSQL automatically changes column to NOT NULL, creates a sequence tablename_serialcol_seq and DEFAULT NEXTVAL to select ID values from the sequence but if they are non supplied in INSERT argument:
CREATE Table teams ( id SERIAL, name VARCHAR (90) ); -- is equivalent to CREATE SEQUENCE teams_id_seq; CREATE TABLE teams ( id INT Non NULL DEFAULT NEXTVAL ( 'teams_id_seq' ) , proper noun VARCHAR (ninety) );
If yous demand a SERIAL column to exist unique, you have to specify UNIQUE or PRIMARY KEY explicitly.
To generate a ID value, yous can omit the SERIAL column in INSERT statement, or specify DEFAULT keyword:
-- Omit serial column INSERT INTO teams (proper name) VALUES ( 'Aston Villa' ); -- Specify DEFAULT INSERT INTO teams VALUES ( DEFAULT , 'Manchester City' );
Note that you cannot insert NULL, but can insert 0. In MySQL these two values forcefulness ID generation, but this is not applied to PostgerSQL:
INSERT INTO teams VALUES ( Naught , 'Some squad' ); -- ERROR: nix value in column "id" violates not-null constraint INSERT INTO teams VALUES ( 0 , 'Reserved' ); -- ane row affected
Tabular array content:
| id | name |
| one | Tottenham Hotspur |
| 2 | Aston Villa |
| three | Manchester Urban center |
| 0 | Reserved |
SERIAL - Specify Initial Value and Increment
PostgreSQL Serial information blazon does not provide options to set up the start value and increment, but you tin can change the sequence object assigned to SERIAL using ALTER SEQUENCE argument:
CREATE TABLE teams2 ( id SERIAL UNIQUE , proper name VARCHAR (90) ); -- Alter initial value and increment Modify SEQUENCE teams2_id_seq RESTART WITH iii Increase Past 3; -- Insert data INSERT INTO teams2 (proper noun) VALUES ( 'Crystal Palace' ); INSERT INTO teams2 (name) VALUES ( 'Leeds United' );
Table content:
| id | proper noun |
| three | Crystal Palace |
| 6 | Leeds United |
Serial - Retrieving Generated ID
There are several options to obtain the inserted ID value. You lot tin can use LASTVAL() function that returns the latest value for any sequence:
INSERT INTO teams (name) VALUES ( 'Manchester United' ); SELECT LASTVAL( ); -- Returns: 4
You tin also obtain the current value from the sequence object directly using CURRVAL function. The sequence name is tablename_serialcol_seq:
INSERT INTO teams (name) VALUES ( 'Chelsea' ); SELECT CURRVAL( 'teams_id_seq' ); -- Returns: five
Or you can use the RETURNING clause of INSERT argument to render ID:
INSERT INTO teams (name) VALUES ( 'Armory' ) RETURNING id; -- Returns: 6
How to Access Generated ID in Application
SELECT LASTVAL() and SELECT CURRVAL render the generated ID as a single-row result set. In a .Internet, Java or PHP awarding you can utilise advisable methods to execute a query and read a row:
If you lot need to obtain ID in another PostgreSQL PL/pgSQL function, you tin employ INTO clause:
DECLARE last_id INT; ... INSERT INTO teams VALUES ( ... ); SELECT LASTVAL( ) INTO last_id; -- or INSERT INTO teams VALUES ( ... ); SELECT CURRVAL( 'teams_id_seq' ) INTO last_id; -- or in a unmarried argument INSERT INTO teams VALUES ( ... ) RETURNING id INTO last_id;
SERIAL - Make a Gap
If yous insert an ID value explicitly, it has no result on the sequence generator, and its next value remains unchanged and will be used when you insert subsequent rows:
-- Insert ID eight explicitly INSERT INTO teams VALUES ( 8 , 'Everton' ); -- Keep using ID generator INSERT INTO teams (name) VALUES ( 'Liverpool' ); -- ID 7 is assigned
Note that the sequence generator may accept conflicts with IDs that were already inserted using explicit values. You can get an mistake if there is an UNIQUE constraint, or duplicate IDs tin exist inserted:
-- Will try to assign ID eight that already inserted INSERT INTO teams (name) VALUES ( 'Some team' ); -- ERROR: duplicate central value violates unique constraint "teams_id_key" -- Particular: Key (id)=(8) already exists. -- Continue, now it will use ID 9 INSERT INTO teams (proper name) VALUES ( 'Newcastle United' );
Table content:
| id | name |
| one | Tottenham Hotspur |
| 2 | Aston Villa |
| 3 | Manchester City |
| 0 | Reserved |
| iv | Manchester United |
| five | Chelsea |
| half-dozen | Arsenal |
| eight | Everton |
| vii | Liverpool |
| 9 | Newcastle United |
If you remove rows from a table, you can insert removed IDs explicitly, it will not have any result on the sequence generator.
SERIAL - Restart or reset ID
You can alter the current value of a sequence generator using ALTER SEQUENCE statement:
ALTER SEQUENCE teams_id_seq RESTART WITH 31; -- ID 31 volition be assigned INSERT INTO teams (name) VALUES ( 'Queens Park Rangers' );
PostgreSQL Serial in Other Databases
Serial (identity or machine-increment) columns in other databases:
Oracle:
| Auto-increment or Identity | | Tin can be emulated using a sequence and trigger |
SQL Server:
| IDENTITY(start, increment) | | Increment tin exist specified |
MySQL:
| AUTO_INCREMENT Column Option | ||
| Start Value | | |
| Increment | | Always i |
| Generate ID | NULL or 0 force ID generation | |
| Last ID | LAST_INSERT_ID() function | |
| Restrictions | UNIQUE or Primary KEY constraint is required | |
PostgreSQL Series Conversion to Other Databases
Converting PostgreSQL Serial columns:
Oracle:
Oracle does not back up Series (auto-increase, identity) columns, merely this functionality can exist implemented using a sequence and a trigger:
CREATE TABLE teams ( id NUMBER (10,0) UNIQUE , proper noun VARCHAR2 (90) ); CREATE SEQUENCE teams_id_seq START WITH ane Increment BY 1; CREATE OR REPLACE TRIGGER teams_seq_tr Before INSERT ON teams FOR EACH ROW WHEN (NEW.id IS Zilch ) Brainstorm SELECT teams_id_seq. NEXTVAL INTO :NEW.id FROM dual; END; /
Notation that a trigger is required as Oracle does not let using NEXTVAL in DEFAULT clause for a column.
SQL Server:
SQL Server supports IDENTITY property and allows you lot to specify the initial and increment values:
CREATE TABLE teams ( id INT IDENTITY (1, 1) UNIQUE , proper name VARCHAR (ninety) );
MySQL:
MySQL supports AUTO_INCREMENT cavalcade option that allows y'all to automatically generate IDs.
There is the table option AUTO_INCREMENT that allows you to define the starting time value, but you cannot define the increment, it is always 1:
CREATE TABLE teams ( id INT AUTO_INCREMENT UNIQUE , name VARCHAR (ninety) ) AUTO_INCREMENT = ane; -- start value
Note that MySQL requires an unique or primary key constraint on AUTO_INCREMENT columns.
For more information, run across Generating IDs in MySQL.
Resources
PostgreSQL 9.1 Documentation
Source: http://www.sqlines.com/postgresql/datatypes/serial