EDUCBA

Home » Data Science » Data Science Tutorials » PostgreSQL Tutorial » PostgreSQL SERIAL

PostgreSQL SERIAL

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 serial data type 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, 647 number in our table by 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, 807 and storage size is 8 bytes.

Syntax:

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

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 into 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, first PostgreSQL will 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 was assigning the owner to the column. We can change the owner of the sequence using alter command.
  • PostgreSQL will provide three serial types are as SMALLSERIAL, SERIAL, 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, 647 number in our table by 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, 807 and 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 column in a table; it will generate a serial sequence of integer numbers.
  • Using serial data type, it will not automatically create the index on column; we need to create it explicitly.
  • We can check the existing sequence by using the pg_get_serial_sequence function.
  • Below figure shows that display the emp_serial1_id_seq function by using pg_get_serial_sequence function.
  • We need to pass the table name in the first argument and column name in the second argument in the same function.

Code:

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

Output:

PostGRESQL SERIAL1

Examples of PostgreSQL SERIAL

Given below are the examples:

Example #1

Define serial data type at the time of table creation.

  • The below example shows define a serial data type to the column at the time of table creation.
  • We have defined 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:

PostGRESQL SERIAL2

Example #2

Alter 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 serial data type on the emp_id column after the creation of the table.

Code:

ALTER TABLE Emp_Serial drop column emp_id;

Output:

alter table

Code:

ALTER TABLE Emp_Serial ADD column emp_id SERIAL;

Output:

alter table

Code:

\d+ Emp_Serial;

Output:

postgreSQL SERIAL5JPG

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 have created the table name as 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 to this sequence.

Code:

CREATE SEQUENCE EMP_SERIAL1_id_seq;

Output:

using creating a sequence

Code:

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

Output:

postgreSQL SERIAL7JPG

Code:

ALTER SEQUENCE EMP_SERIAL1_id_seq OWNED BY Emp_Serial1.emp_id;

Output:

using creating a sequence

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:

postgreSQL SERIAL9JPG

Code:

ALTER SEQUENCE emp_serial_emp_id_seq RESTART WITH 51;

Output:

Alter table to restart value

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:

Alter table to restart value

Code:

select * from Emp_Serial;

Output:

postgreSQL SERIAL12JPG

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.

Recommended Articles

This is a guide to PostgreSQL SERIAL. Here we discuss the introduction to PostgreSQL SERIAL and how the function works in this with the examples. You may also have a look at the following articles to learn more –

  1. PostgreSQL ROLLUP
  2. PostgreSQL IN Operator
  3. PostgreSQL FETCH
  4. PostgreSQL DISTINCT
  5. PostgreSQL HAVING
  6. Guide to PostgreSQL RANDOM
0 Shares
Share
Tweet
Share