PostgreSQL: serial vs identity

 The Evolution: Serial to Identity

The "serial" implementation, a longtime companion in PostgreSQL, has proven effective in generating unique values for primary key columns. However, this method lacks conformity with the SQL standard. In response to this, PostgreSQL 10 introduced the "generated as identity" syntax, aligning with SQL standards while maintaining the underlying sequence-based implementation.

Consider the following example:

1. -- Using "serial" (old implementation)  
2. CREATE TABLE t1 (id serial primary key);  
3.   
4. -- Using "generated as identity" (new implementation)  
5. CREATE TABLE t2 (id integer primary key generated always as identity);  

The key distinction lies in how the sequences are managed and how the system prevents accidental overrides.

Title: Evolving with PostgreSQL 10: Embracing Identity Columns for Better Standards Compliance

Introduction:

PostgreSQL, a stalwart in the realm of relational databases, has continuously evolved to meet industry standards and user expectations. In PostgreSQL 10, a significant enhancement was introduced, bringing about a shift from the conventional "serial" implementation to a more SQL standard-compliant syntax using "generated as identity." This not only aligns with best practices but also enhances data integrity and security. In this blog post, we'll explore the transition from "serial" to "generated as identity" and the advantages it brings.

---

The Evolution: Serial to Identity

The "serial" implementation, a longtime companion in PostgreSQL, has proven effective in generating unique values for primary key columns. However, this method lacks conformity with the SQL standard. In response to this, PostgreSQL 10 introduced the "generated as identity" syntax, aligning with SQL standards while maintaining the underlying sequence-based implementation.

Consider the following example:

1. -- Using "serial" (old implementation)  
2. CREATE TABLE t1 (id serial primary key);  
3.   
4. -- Using "generated as identity" (new implementation)  
5. CREATE TABLE t2 (id integer primary key generated always as identity);  

The key distinction lies in how the sequences are managed and how the system prevents accidental overrides.

---

Ensuring Integrity with Identity Columns

One of the critical advantages of the "generated as identity" syntax is its ability to prevent accidental overrides of values. In the example with table t1, an insert operation without advancing the sequence can lead to desynchronization between the sequence and table values, causing potential errors in subsequent inserts.

However, with the use of the "generated as identity" syntax in table t2, attempting to insert a specific value directly into the identity column results in an error. This ensures that users are aware of the identity column's nature and prevents inadvertent sequence mismatches.

1. -- Attempting to insert a specific value into an identity column  
2. INSERT INTO t2 (id) VALUES (1); -- Results in an error  

Even if there's a need to force an insert with a specific value, the system value override option provides a way to do so while still notifying users about the potential sequence desynchronization.

1. -- Forcing an insert with a specific value in an identity column  
2. INSERT INTO t2 (id) OVERRIDING SYSTEM VALUE VALUES (1);  

Simplifying Permissions with Identity Columns

Identity columns also bring a notable advantage in terms of permissions. Unlike tables using "serial" columns, where both INSERT and USAGE privileges on the underlying sequence are required, tables using identity columns streamline permissions. Granting the INSERT privilege alone is sufficient, reducing the complexity of access control.

1. -- Granting permissions for a table with a serial column  
2. GRANT INSERT, USAGE ON SEQUENCE t1_id_seq TO user;  
3.   
4. -- Granting permissions for a table with an identity column  
5. GRANT INSERT ON t2 TO user;  

This simplified permission model enhances security and eases the management of access control in PostgreSQL databases.