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.
column2 are the columns you wish to include in your new table, drawn from
existing_table using a particular
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
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.
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!