PostgreSQL – CREATE TABLE AS Statement

ByAdmin

The CREATE TABLE AS statement is used to create a new table based on the result of a query. It allows you to create a new table that holds the data returned by a SELECT query or a subset of the data from an existing table.

Here’s the basic syntax of the CREATE TABLE AS statement:

CREATE TABLE new_table AS 
SELECT columns 
FROM existing_table 
WHERE conditions;

Here, new_table is the name of the new table you want to create.

columns are the columns you want to select from an existing table or a query result.

and existing_table is the name of the existing table you want to select data from, or a complete SELECT query.

Here’s an example of using CREATE TABLE AS to create a new table based on the result of a query:

Assume you have an existing table named employees:

CREATE TABLE employees 
(employee_id serial PRIMARY KEY, 
first_name text, 
last_name text, 
salary numeric );

You can create a new table high_paid_employees that contains information about employees with a salary greater than 50000:

CREATE TABLE high_paid_employees AS SELECT * FROM employees WHERE salary > 50000;

In this example, the high_paid_employees table will contain the same columns as the employees table and only the rows that meet the condition of having a salary greater than 50000.

The data types of the columns in the new table are determined by the result of the query.

The new table will not automatically have indexes, constraints, or other properties that might exist in the original table.

If you want to create a table that has the same structure as an existing table but without copying data, you can use the LIKE clause:

CREATE TABLE new_table (LIKE existing_table);

The CREATE TABLE AS statement is a convenient way to create a new table based on query results, which can be useful for aggregations, data transformations, and creating subsets of data for analysis or reporting.

Similar Posts

PostgreSQL – Create Table

ByAdmin

Below is the sql command syntax to create a table in PostgreSQL database: CREATE TABLE table_name ( column1 datatype1 constraint1, column2 datatype2 constraint2, column3 datatype3 constraint3, … ); Below is an example of how to create a table in PostgreSQL database with two columns: CREATE TABLE employees ( employee_id integer, employee_name text ); Above sql…