In the world of database management, PostgreSQL stands out as one of the most robust and versatile systems. We are going to deep dive into one of the most essential commands – the PostgreSQL CREATE TABLE AS command. This guide will provide a comprehensive look at how to use this command, its syntax, and how it fits into broader database management practices.

The PostgreSQL Create Table As Command Explained

At its core, the CREATE TABLE AS command allows you to create a new table from the results of a query. This comes in handy when you need to store a result set for future reference, perform complex operations, or reduce the execution time of repeated queries.

The basic syntax is as follows:

				
					CREATE TABLE new_table AS
    SELECT column1, column2,...
    FROM existing_table
    WHERE condition;

In this code, new_table is the name of the table you want to create. column1, column2 are the columns you wish to include in your new table, drawn from existing_table using a particular condition.

Delving Deeper: Create Table As Command Examples

To help you understand how the CREATE TABLE AS command works, let’s go through a couple of illustrative examples.

Suppose we have a table named employees with the following structure:

				
					CREATE TABLE employees(
    emp_id INT PRIMARY KEY,
    first_name VARCHAR (40),
    last_name VARCHAR (40),
    birth_day DATE,
    sex VARCHAR (1),
    salary INT,
    super_id INT,
    branch_id INT
);

Let’s say we want to create a new table to store the details of employees with a salary greater than 50000. We can use the CREATE TABLE AS command as follows:

				
					CREATE TABLE high_paid_employees AS
    SELECT emp_id, first_name, last_name, salary
    FROM employees
    WHERE salary > 50000;

In this example, the new high_paid_employees table will contain the emp_id, first_name, last_name, and salary of all employees earning more than 50000.

Advanced Use: Combining Create Table As with Other Commands

The CREATE TABLE AS command in PostgreSQL can also be combined with other SQL commands to extend its functionality.

For instance, to create a temporary table that contains the average salary of employees by sex, you can use the CREATE TEMP TABLE command combined with CREATE TABLE AS as follows:

				
					CREATE TEMP TABLE avg_salary_by_sex AS
    SELECT sex, AVG(salary)
    FROM employees
    GROUP BY sex;

Here, the temporary table avg_salary_by_sex will store the average salary of employees, grouped by sex.

Wrap up

The CREATE TABLE AS command is a powerful tool in PostgreSQL, providing a straightforward way to create new tables based on existing data and queries. It offers efficient storage of query results for later use and enables complex operations that require temporary or permanent storage of derived data.

Understanding and mastering this command is critical for any database administrator or developer working with PostgreSQL. It showcases the flexibility and power of PostgreSQL, contributing significantly to its reputation as a robust database management system.

Check how to install PostgreSQL: https://softwareto.tech/how-install-postgresql-on-windows/


Thanks for reading. Happy coding!