PostgreSQL SERIAL

Updated May 8, 2023

Introduction to PostgreSQL SERIAL

PostgreSQL serial data type is used to define the auto-increment number of column in a table; PostgreSQL serial will generate a serial sequence of integer numbers. We can also restart serial no after the creation of the table using alter command, the storage size of the serial data type is 4 bytes, and the range of serial data type in PostgreSQL is 1 to 2, 147, 483, 647. Using a serial data type, we can store up to 2, 147, 483, and 647 numbers in our table. If we use bigserial, then the range of this serial data type is 1 to 9, 223, 372, 036, 854, 775, and 807, and the storage size is 8 bytes.

Syntax:

Below is the syntax:

CREATE TABLE table_name (Table name that we have used to create new table) (column_name1(Column name on which we have creating a Serial data type)SERIAL, column_name2 data_type,column_nameN data_type ()Data type that we defined to column);

Serial is equivalent to the following statement(Sequence) in PostgreSQL.

CREATE SEQUENCE(Create new sequence) table_name_id_seq (Sequence name );

CREATE TABLE table_name (Table name that we have used to create new table)
(Column_nameinteger (Data type) NOT NULL (Defined not null constraint to the column) DEFAULTNextval ('table_name_id_seq'));

ALTER SEQUENCE (Alter sequence using alter command) table_name_id_seq
OWNED BY table_name.column_name;

Parameter description of the above syntax is as follows:

• Create: Create a table by using serial data type in PostgreSQL. We can define serial data type in a table column.
• Column 1 to column N: Column name used while creating a column in PostgreSQL. In this column, we have defined the serial data type.
• Data type: Data type defines the type of data we have stored in the table. A data type is most important while creating a table.
• Table name: Table name on which column we have defining serial data type.
• Serial: Data type is used to define auto increment number of column in a table; PostgreSQL serial will generate a serial sequence of integer numbers.
• Sequence: Equivalent to a serial data type, we have created a sequence in PostgreSQL.
• Sequence name: We have created a new sequence equivalent to a serial data type in PostgreSQL.
• Alter sequence: We have altered the sequence and changed the sequence number in PostgreSQL.

How PostgreSQL SERIAL Function Works?

• After defining a serial data type to the column, postgresql will first create a sequence and set the next value generated by this sequence.
• The second time it will add a not-null constraint on the serial column because it always generates an integer value.
• The third time it assigned the owner to the column. We can change the owner of the sequence using alter command.
• PostgreSQL will provide three serial types: SMALLSERIAL, SERIAL, and BIGSERIAL.
• The serial data type’s storage size is 4 bytes, and the range of serial data type in PostgreSQL is 1 to 2, 147, 483, 647. We can store up to 2, 147, 483, and 647 numbers in our table using a serial data type.
• If we use bigserial, then the range of this serial data type is 1 to 9, 223, 372, 036, 854, 775, and 807, and the storage size is 8 bytes.
• If we use data type as a small serial, then the range of serial data type is 1 to 32, 767.
• PostgreSQL serial data type is used to define the auto increment number of columns in a table; it will generate a serial sequence of integer numbers.
• Using serial data type will not automatically create the index on the column; we need to create it explicitly.
• We can check the existing sequence by using the pg_get_serial_sequence function.
• The figure below shows the emp_serial1_id_seq function by using the pg_get_serial_sequence function.
• We need to pass the table name in the first argument and the column name in the second argument in the same function.

Code:

select pg_get_serial_sequence('Emp_Serial1','emp_id');

Output:

Examples of PostgreSQL SERIAL

Given below are the examples:

Example #1

Define serial data type at the time of table creation.

• The example below defines a serial data type to the column at the table creation time.
• We have defined the serial data type on the emp_id column at the time of table creation. Creating this data type on the column “emp_serial_emp_id_seq” is automatically created.

Code:

CREATE TABLE Emp_Serial (emp_id SERIAL NOT NULL, emp_name character(10) NOT NULL, emp_address character(20) NOT NULL, emp_phone character(14), emp_salary INT NOT NULL, date_of_joining date NOT NULL);

Output:

Example #2

Alter the table to add serial data type after table creation.

• The below example shows define a serial data type to the column after table creation.
• We have defined the serial data type on the emp_id column after the creation of the table.

Code:

ALTER TABLE Emp_Serial drop column emp_id;

Output:

Code:

ALTER TABLE Emp_Serial ADD column emp_id SERIAL;

Output:

Code:

\d+ Emp_Serial;

Output:

Example #3

Create serial data type using creating a sequence.

• We have created a serial data type using a sequence. We have created the sequence name as emp_serial1_id_seq.
• After sequence creation, we created the table name emp_serial1 and defined the next value as a sequence name.
• After creating a table, we have altered a sequence name and defined a table name and column name for this sequence.

Code:

CREATE SEQUENCE EMP_SERIAL1_id_seq;

Output:

Code:

CREATE TABLE Emp_Serial1 (emp_id integer NOT NULL DEFAULT Nextval ('Emp_Serial1_id_seq'));

Output:

Code:

ALTER SEQUENCE EMP_SERIAL1_id_seq OWNED BY Emp_Serial1.emp_id;

Output:

Example #4

Alter table to restart value of serial data type.

• The below example shows that we have to alter the Emp_Serial table after inserting the first value. After inserting the first value, we have altered the serial value of the table.

Code:

INSERT INTO Emp_Serial (emp_name, emp_address, emp_phone, emp_salary, date_of_joining) VALUES ('ABC', 'Pune', '1234567890', 20000, '01-01-2020');

Output:

Code:

ALTER SEQUENCE emp_serial_emp_id_seq RESTART WITH 51;

Output:

Code:

INSERT INTO Emp_Serial (emp_name, emp_address, emp_phone, emp_salary, date_of_joining) VALUES ('ABC', 'Pune', '1234567890', 20000, '01-01-2020');

Output:

Code:

select * from Emp_Serial;

Output:

Conclusion

PostgreSQL serial data type is used to define the auto increment number of column in a table; PostgreSQL serial will generate a serial sequence of integer numbers. We can also restart the serial number after creating a table using alter command in PostgreSQL; the serial data type’s storage size is 4 bytes.

We hope that this EDUCBA information on “PostgreSQL SERIAL” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

1. PostgreSQL ROLLUP
2. PostgreSQL IN Operator
3. FETCH PostgreSQL
4. Guide to PostgreSQL RANDOM