How do you create a table with a computed column that derives its value from other columns?
LeoRobs
Posted by LeoRobs
Last Updated: July 16, 2024
table computed
Creating a table with a computed column in SQL can vary slightly depending on the database management system (DBMS) you're using, but I'll provide the general syntax and examples for a few popular systems, such as SQL Server, MySQL, and PostgreSQL.
SQL Server
In SQL Server, you can create a computed column as follows: 
CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    FullName AS (FirstName + ' ' + LastName)  -- Computed column
);
In this example, FullName is a computed column that concatenates FirstName and LastName.
MySQL
In MySQL, you can create a generated column: 
CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    FullName VARCHAR(101) AS (CONCAT(FirstName, ' ', LastName)) STORED  -- Generated column
);
Here, FullName is defined as a generated column that calculates its value using the CONCAT function.
PostgreSQL
PostgreSQL does not have a specific computed column feature like SQL Server or MySQL, but you can use a VIEW or create a function to achieve similar functionality. Here’s an example using a view: 
CREATE TABLE Employees (
    EmployeeID SERIAL PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50)
);

CREATE VIEW EmployeeFullNames AS
SELECT EmployeeID, FirstName, LastName, (FirstName || ' ' || LastName) AS FullName
FROM Employees;
In this case, the FullName is part of the view but not part of the table structure itself.
Summary
- SQL Server: Use AS to declare a computed column. - MySQL: Use AS and specify if it’s STORED or VIRTUAL. - PostgreSQL: You would typically use a view to represent computed data. Always check your specific DBMS documentation for the most accurate syntax and features available, as there could be occasional variations.
Default Image