SQL Reference - GENERATED

Example

CREATE TABLE orders (
    order_id INT AUTO_INCREMENT,
    product_name VARCHAR(100),
    quantity INT,
    price DECIMAL(10,2),
    total_price DECIMAL(10,2) AS (quantity * price),
PRIMARY KEY(order_id)
);

INSERT INTO orders (product_name, quantity, price)
VALUES('Laptop', 5, 1000.00);

SELECT * FROM orders;

Output

+----------+--------------+----------+-------+-------------+
| order_id | product_name | quantity | price | total_price |
+----------+--------------+----------+-------+-------------+
|        1 | Laptop       |        5 | 1000.00 |    5000.00 |
+----------+--------------+----------+-------+-------------+

Explanation

In the example, the total_price column is a generated column that automatically calculates and stores the total price of each order by multiplying the quantity and price. When a new record is inserted into the orders table, the value for the total_price column is automatically calculated and stored in the total_price column.

Example

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    first_name varchar(50),
    last_name varchar(50),
    full_name varchar(101) GENERATED ALWAYS AS (first_name || ' ' || last_name) STORED
);

INSERT INTO employees (first_name, last_name) VALUES ('John', 'Doe');
SELECT * FROM employees;

Output

 id | first_name | last_name | full_name
----+------------+-----------+-----------
  1 | John       | Doe       | John Doe

Explanation

The GENERATED ALWAYS AS keyword is used to automatically generate a value for a specific column based on the values in other columns. In this case, the full_name column is automatically generated as a combination of the first_name and last_name columns. The STORED keyword means the generated values are physically stored in the table.

Example

CREATE TABLE employees (
    id number,
    name varchar(100),
    dob date,
    age as (months_between(sysdate, dob)/12)
);

Output

Table EMPLOYEES created.

Explanation

In the SQL code snippet, a new table named employees is created. This table contains three explicit columns id, name, dob, and a calculated column age. The GENERATED ALWAYS syntax used for the age column means it always appears with the value calculated from the expression (months_between(sysdate, dob)/12). This means, age is calculated automatically based on the date of birth (dob) and the current date (sysdate), and the value cannot be manually set or altered.