Create Table As in PostgreSQL

In PostgreSQL, you can use the CREATE TABLE AS statement to create a new table based on the result set of a query. This is similar to the CREATE TABLE AS statement in other database systems like MySQL, Oracle, and Teradata.

Here's the syntax for CREATE TABLE AS in PostgreSQL:

CREATE TABLE new_table_name AS

SELECT column1, column2, ...

FROM existing_table_name;

Here's an example:

Let's say you have an existing table called employees with columns employee_id, first_name, last_name, salary, and department_id, and you want to create a new table called high_salary_employees to store information about employees with a salary higher than $100,000.

You can use the CREATE TABLE AS statement as follows:

CREATE TABLE high_salary_employees AS

SELECT employee_id, first_name, last_name, salary, department_id

FROM employees

WHERE salary > 100000;

This statement will create a new table called high_salary_employees with the same columns as the employees table, and it will contain only the rows where the salary column is greater than $100,000.

It's important to note that the new table created with CREATE TABLE AS will inherit data types, lengths, and nullability from the result set of the query. Additionally, the new table will not inherit any indexes, primary keys, constraints, or other database objects from the source table. You may need to recreate those objects on the new table if needed.