PostgreSQL – SERIAL Data Type

The serial data type is not a true data type like integer or text. Instead, it is a shortcut for creating an auto-incrementing integer column in a table. It is commonly used to create primary key columns that automatically generate unique values when new rows are inserted. The serial type is used in combination with the SERIAL or BIGSERIAL column attributes. Here are some key points about the serial data type:

1. SERIAL vs. BIGSERIAL:
   • SERIAL: This creates a 32-bit auto-incrementing integer column. It’s appropriate for tables where the expected number of rows won’t exceed about two billion.
   • BIGSERIAL: This creates a 64-bit auto-incrementing integer column. It’s suitable for tables that might need to handle a very large number of rows.
2. Usage: You use the SERIAL or BIGSERIAL data type when defining the primary key or any other column in the table that requires an auto-incrementing unique value.
3. Auto-incrementing Values: When a new row is inserted without specifying a value for the SERIAL or BIGSERIAL column, PostgreSQL will automatically generate a unique value for that column.
4. Sequences: Under the hood, PostgreSQL uses a sequence to generate the auto-incrementing values for SERIAL and BIGSERIAL columns. You can access and manipulate sequences using various functions.
5. Serial with Default: You can also use the DEFAULT clause with a SERIAL column to set an initial value for the auto-incrementing sequence.

CREATE TABLE orders 
( order_id SERIAL PRIMARY KEY DEFAULT 1000, 
order_date DATE, 
total_amount DECIMAL(10, 2) );

Here’s an example of using the SERIAL data type:

CREATE TABLE products 
( product_id SERIAL PRIMARY KEY, 
product_name VARCHAR(100), 
price DECIMAL(10, 2) ); 

INSERT INTO products (product_name, price) VALUES ('Widget A', 19.99), ('Widget B', 29.99); 

SELECT * FROM products WHERE product_id = 1;

Here, a product_id column of type SERIAL is used as the primary key in the products table. This column will automatically generate unique, incrementing values for each new row inserted.