Introduction to PostgreSQL serial Data Type

The serial data type in PostgreSQL is a special data type used for automatically generating sequential numbers. This data type is very convenient, especially when dealing with tables that require auto-incrementing primary keys, as it can automatically handle incrementing values without manual input.

Syntax

In PostgreSQL, SERIAL can be used as a data type for a column to create an auto-incrementing sequence. For example, the following statement creates a table named users with an auto-incrementing column id:

CREATE TABLE users (
   id SERIAL PRIMARY KEY,
   name VARCHAR(50) NOT NULL,
   email VARCHAR(255) NOT NULL
);

This will create an auto-incrementing integer column id as the primary key.

Use Cases

The main use case for using the serial data type is for columns that require auto-incrementing primary keys. By using the serial data type, you can avoid manually inserting incrementing sequence values and have them generated automatically by PostgreSQL.

Examples

Here are two examples of using the serial data type.

Example 1: Creating a table with a serial column

Let’s say we want to create a table named books with the following columns: id, title, and author. The id column will be the primary key and auto-incrementing.

First, we can create the books table using the following command:

CREATE TABLE books (
   id SERIAL PRIMARY KEY,
   title VARCHAR(255) NOT NULL,
   author VARCHAR(255) NOT NULL
);

This will create an auto-incrementing integer column id as the primary key.

Next, we can insert some data using the following command:

INSERT INTO books (title, author)
VALUES
   ('The Great Gatsby', 'F. Scott Fitzgerald'),
   ('To Kill a Mockingbird', 'Harper Lee'),
   ('1984', 'George Orwell');

This will insert 3 rows of data, each with values for the title and author columns. Since we did not specify a value for the id column, PostgreSQL will automatically generate an incrementing id value for each row.

Example 2: Retrieving the current sequence value using currval() function

In PostgreSQL, the currval() function can be used to retrieve the current value of the last inserted serial column. For example, let’s say we already have a table books with a serial column id and some data has been inserted. To retrieve the id value of the last inserted row, you can use the following command:

SELECT currval('books_id_seq');

This will return the id value of the last inserted row.

Conclusion

The serial data type is a very convenient data type for automatically generating sequential numbers. It is especially useful for tables that require auto-generating primary keys. By allowing PostgreSQL to automatically handle incrementing values, using the serial data type can greatly simplify code.