PostgreSQL - Identity Column

In PostgreSQL, an identity column is a column that automatically generates and populates values based on a sequence, similar to the AUTO_INCREMENT column in MySQL or the IDENTITY column in SQL Server. Starting from PostgreSQL 10, the identity column was introduced as a more SQL standard-conforming replacement for the older SERIAL columns.

Advantages of Identity Column over SERIAL:

Clear Intent: When you define a column as an identity column, you're making it clear that the column is meant for auto-generated values.
More Control: Unlike SERIAL which is a pseudo-type, identity columns allow for better control over the underlying sequence.

Creating a Table with an Identity Column:

Here's the basic syntax:

CREATE TABLE table_name (
    column_name data_type GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY
    ...
);

GENERATED ALWAYS: Means the system always generates a value for the column; you can't insert or update a value for this column.
GENERATED BY DEFAULT: Means the system generates a value only when a value is not provided during an insert or update operation.

Example:

CREATE TABLE employees (
    emp_id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    name varchar(100) NOT NULL
);

In this example, whenever you insert a new row into the employees table without specifying a value for emp_id, PostgreSQL will automatically generate a value for it.

Using the Identity Column:

Inserting a Row:
Since emp_id is defined as GENERATED BY DEFAULT, you can either provide a value or let the system generate one:
```
INSERT INTO employees (name) VALUES ('John Doe');
```
or
```
INSERT INTO employees (emp_id, name) VALUES (5, 'Jane Smith');
```
Altering an Identity Column:
You can alter an existing column to add an identity specification:
```
ALTER TABLE employees 
ALTER COLUMN column_name 
ADD GENERATED ALWAYS AS IDENTITY;
```
Modifying the Sequence:
The sequence associated with an identity column can be altered to change its behavior. For example, if you want to restart the sequence:
```
ALTER TABLE table_name 
ALTER COLUMN column_name 
RESTART WITH 100;
```

Note:

If you're transitioning from older PostgreSQL versions or from other databases, remember that SERIAL columns were commonly used before identity columns. While SERIAL columns work and are still supported, for new developments, it's a good practice to use identity columns due to their clearer semantics and better alignment with SQL standards.

How to create an Identity column in PostgreSQL:
- Description: Create a table with an Identity column.
- Code:
```
CREATE TABLE users (
    user_id SERIAL PRIMARY KEY,
    username VARCHAR(255)
);
```

Defining seed and increment values for Identity column in PostgreSQL:

Description: Specify seed and increment values for an Identity column.

Code:

CREATE TABLE orders (
    order_id INT GENERATED ALWAYS AS IDENTITY (START WITH 1000 INCREMENT BY 5) PRIMARY KEY,
    total_amount DECIMAL(10,2)
);

Inserting data into tables with Identity columns in PostgreSQL:
- Description: Insert data into a table with an Identity column.
- Code:
```
INSERT INTO users (username) VALUES ('john_doe'), ('jane_doe');
```
Updating Identity column values in PostgreSQL:
- Description: Update values of an Identity column.
- Code:
```
UPDATE users SET user_id = user_id + 1000 WHERE username = 'john_doe';
```
Retrieving the last inserted Identity value in PostgreSQL:
- Description: Retrieve the last inserted Identity value.
- Code:
```
INSERT INTO orders (total_amount) VALUES (500.00) RETURNING order_id;
```
Dropping Identity columns in PostgreSQL:
- Description: Remove an Identity column from a table.
- Code:
```
ALTER TABLE users DROP COLUMN user_id;
```

Using Identity columns in table constraints in PostgreSQL:

Description: Apply constraints to Identity columns.

Code:

CREATE TABLE products (
    product_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    product_name VARCHAR(255) UNIQUE
);

Identity column and foreign key relationships in PostgreSQL:
- Description: Establish a foreign key relationship involving an Identity column.
- Code:
```
CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    user_id INT REFERENCES users(user_id)
);
```

Identity columns with composite primary keys in PostgreSQL:

Description: Use Identity columns in tables with composite primary keys.

Code:

CREATE TABLE employees (
    department_id INT,
    employee_id INT GENERATED ALWAYS AS IDENTITY,
    PRIMARY KEY (department_id, employee_id)
);

Identity column and data types in PostgreSQL:

Description: Specify data types for Identity columns.

Code:

CREATE TABLE transactions (
    transaction_id BIGSERIAL PRIMARY KEY,
    transaction_date TIMESTAMP
);

Altering Identity column properties in PostgreSQL:
- Description: Modify properties of an existing Identity column.
- Code:
```
ALTER TABLE orders ALTER COLUMN order_id RESTART WITH 2000;
```