PostgreSQL – SERIAL

PostgreSQL has a special kind of database object generator called SERIAL. It is used to generate a sequence of integers which are often used as Primary key of a table.

Syntax: variable_name SERIAL

When creating a table, this sequence of integers can be created as follows:

CREATE TABLE table_name(
    id SERIAL
);

By assigning the SERIAL to the id column, PostgreSQL carries out the following:

  • First, create a sequence object and set the next value generated by the sequence as the default value for the column.
  • Second, add a NOT NULL constraint to the id column because a sequence always generates an integer, which is a non-null value.
  • Third, assign the owner of the sequence to the id column; as a result, the sequence object is deleted when the id column or table is dropped

PostgreSQL supports 3 serial pseudo-types namely SMALLSERIAL, SERIAL and BIGSERIAL which has the following characteristics:

Name Storage Size Range
SMALLSERIAL 2 bytes 1 to 32, 767
SERIAL 4 bytes 1 to 2, 147, 483, 647
BIGSERIAL 8 bytes 1 to 9, 223, 372, 036, 854, 775, 807

Example 1:
First we will create a animals table with the id column as the SERIAL column as below:



CREATE TABLE animals(
   id SERIAL PRIMARY KEY,
   name VARCHAR NOT NULL
);

Now while inserting the values to the table one can use anyone of the below commands:

INSERT INTO animals(name) 
VALUES('Dog');

or,

INSERT INTO animals(id, name) 
VALUES(DEFAULT, 'Dog');

So let’s add some rows to our animal table using the command below:

INSERT INTO animals(name) 
VALUES
      ('Dog'),
      ('Cat'),
      ('Cow'),
      ('Tiger'),
      ('Snake'),
      ('Bull');

Now check if id’s are auto-assigned to each row by using the below command:

SELECT * FROM animals;

Output:

Example 2:
First we will create a fruits table with the id column as the SERIAL column as below:

CREATE TABLE fruits(
   id SERIAL PRIMARY KEY,
   name VARCHAR NOT NULL
);

Now while inserting the values to the table one can use anyone of the below commands:

INSERT INTO fruits(name) 
VALUES('Apple');

or,

INSERT INTO fruits(id, name) 
VALUES(DEFAULT, 'Apple');

So let’s add some rows to our fruits table using the command below:

INSERT INTO fruits(name) 
VALUES
      ('Apple'),
      ('Banana'),
      ('Cherry'),
      ('Mango'),
      ('Grapes'),
      ('Orange');

Now check if id’s are auto-assigned to each row by using the below command:

SELECT * FROM fruits;

Output:

My Personal Notes arrow_drop_up

Check out this Author's contributed articles.

If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to contribute@geeksforgeeks.org. See your article appearing on the GeeksforGeeks main page and help other Geeks.

Please Improve this article if you find anything incorrect by clicking on the "Improve Article" button below.


Article Tags :

Be the First to upvote.


Please write to us at contribute@geeksforgeeks.org to report any issue with the above content.