PostgreSQL Column Alias

PostgreSQL Column Alias

The PostgreSQL Column Alias is used to temporarily rename a column heading in the result set of a query. This feature is essential for improving the readability and clarity of query results, especially when using complex expressions or functions.

---

Syntax

SELECT column_name AS alias_name
FROM table_name;

The AS keyword is used to assign an alias to a column. The alias exists only for the duration of the query.

---

Example PostgreSQL Column Alias Queries

Let's look at some examples of PostgreSQL Column Alias queries:

1. Basic Column Alias Example

SELECT first_name AS fname, last_name AS lname
FROM employees;

This query retrieves the first_name and last_name columns from the employees table, but displays them as fname and lname in the result set.

2. Column Alias with Expressions

SELECT salary * 1.1 AS new_salary
FROM employees;

This query calculates a 10% increase on the salary column and displays the result as new_salary in the result set.

3. Column Alias with Functions

SELECT COUNT(*) AS total_employees
FROM employees;

This query counts the total number of rows in the employees table and displays the result as total_employees in the result set.

---

Full Example

Let's go through a complete example that includes creating a table, inserting data, and then using column aliases in a query.

Step 1: Creating a Table

This step involves creating a new table named employees to store employee data.

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    salary NUMERIC(10, 2)
);

In this example, we create a table named employees with columns for id, first_name, last_name, and salary.

Step 2: Inserting Data into the Table

This step involves inserting some sample data into the employees table.

INSERT INTO employees (first_name, last_name, salary)
VALUES ('John', 'Doe', 50000.00);

INSERT INTO employees (first_name, last_name, salary)
VALUES ('Jane', 'Smith', 60000.00);

Here, we insert data into the employees table, including values for the first_name, last_name, and salary columns.

Step 3: Using Column Aliases

This step involves querying the employees table and using column aliases to rename the columns in the result set.

SELECT first_name AS fname, last_name AS lname, salary * 1.1 AS new_salary
FROM employees;

This query retrieves the first_name and last_name columns from the employees table, but displays them as fname and lname. It also calculates a 10% increase on the salary column and displays the result as new_salary in the result set.

Conclusion

The PostgreSQL Column Alias feature is a fundamental tool for improving the readability and clarity of query results. Understanding how to use column aliases and their syntax is essential for effective data presentation and manipulation in PostgreSQL.