Using PostgreSQL Identity Columns for Auto-Incremented IDs

PostgreSQL Identity Column: Auto-Incremented IDs

In PostgreSQL, an identity column provides a way to auto-generate sequential numbers for a table column, often used for primary keys. Similar to auto-increment, identity columns can automatically assign values to new records without requiring the user to specify them, simplifying data management and ensuring unique values for identifiers. PostgreSQL offers two types of identity columns: GENERATED ALWAYS and GENERATED BY DEFAULT.

Syntax and Usage of Identity Columns:

Basic Syntax:

-- Using GENERATED ALWAYS to auto-generate values in PostgreSQL
CREATE TABLE table_name (
    column_name data_type GENERATED ALWAYS AS IDENTITY
);

Types of Identity Columns:

• GENERATED ALWAYS: Prevents manual insertion into the identity column. An error will occur if a value is provided explicitly.
• GENERATED BY DEFAULT: Allows manual insertion, but will generate a value if none is specified.

Example: Creating and Using an Identity Column

Code:

-- Create a table with an identity column for the primary key
CREATE TABLE employees (
    id SERIAL PRIMARY KEY,  -- Auto-generated primary key
    name VARCHAR(100) NOT NULL,
    position VARCHAR(50),
    salary NUMERIC
);
-- Insert data without specifying the id
INSERT INTO employees (name, position, salary) VALUES ('Alice', 'Manager', 60000);
-- The id column is automatically populated

Explanation of Code:

• Creating the Identity Column: The id column is set to auto-generate values, ensuring unique identifiers for each employee.
• Inserting Data: Since id is an identity column, you don’t need to specify its value; PostgreSQL will automatically generate it.

Differences Between Serial and Identity Columns

In PostgreSQL, SERIAL and IDENTITY columns both auto-generate numbers, but they have some distinctions:

• Serial: Uses sequences and creates an implicit sequence, allowing flexible manipulation.
• Identity: Introduced for standard compliance, offers stricter control for applications where column values should not be manipulated.

Additional Notes

• ALTER TABLE: You can alter existing tables to add identity columns if needed.
• Compatibility: Identity columns are recommended for applications needing SQL standard compliance, as they align with SQL standard syntax.

Summary:

Identity columns in PostgreSQL provide a robust, SQL-compliant way to auto-generate unique identifiers. They simplify primary key management and offer a standard approach for applications requiring automatic ID handling.

All PostgreSQL Questions, Answers, and Code Snippets Collection.