How Can I Efficiently Implement Auto-Numbering for Compound Keys in PostgreSQL?

PostgreSQL Compound Key Auto-Numbering: A Simplified Approach

Tracking individual address changes over time requires a unique identifier for each change within a person's history. While complex auto-numbering schemes exist, PostgreSQL offers a cleaner, more efficient solution using serial or IDENTITY columns.

Avoiding Complexity

Traditional methods often lead to intricate, error-prone numbering systems. PostgreSQL's built-in features provide a streamlined alternative.

The Recommended Solution: Serial/IDENTITY Columns

Leveraging PostgreSQL's serial or IDENTITY column simplifies the process significantly. This eliminates the need for managing complex sequences.

Implementation Example

Here's how to implement this in an address_history table:

<code class="language-sql">CREATE TABLE address_history (
  address_history_id serial PRIMARY KEY,
  person_id int NOT NULL REFERENCES people(id),
  created_at timestamptz NOT NULL DEFAULT current_timestamp,
  previous_address text
);</code>

Retrieving Sequential Numbers

To obtain the sequential number for each address change per person, use the row_number() window function:

<code class="language-sql">CREATE VIEW address_history_nr AS
SELECT *, row_number() OVER (PARTITION BY person_id ORDER BY address_history_id) AS adr_nr
FROM   address_history;</code>

This view acts as a direct replacement for the address_history table in queries, providing the sequential number (adr_nr) for each address change.

Key Improvements

• Uses timestamptz (timestamp with time zone) for improved accuracy and consistency.
• Removes redundant address and original_address columns, simplifying the table structure. Only previous_address is necessary.

The above is the detailed content of How Can I Efficiently Implement Auto-Numbering for Compound Keys in PostgreSQL?. For more information, please follow other related articles on the PHP Chinese website!