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:
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:
Differences Between Serial and Identity Columns
In PostgreSQL, SERIAL and IDENTITY columns both auto-generate numbers, but they have some distinctions:
Additional Notes
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.
RetroSearch is an open source project built by @garambo | Open a GitHub Issue
Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo
HTML:
3.2
| Encoding:
UTF-8
| Version:
0.7.4