CREATE TABLE AS

CREATE TABLE AS creates a new table from the result of a select query.

Permissions

The role must have the CREATE permission at the schema level, as well as SELECT permissions for any tables referenced by the statement.

Syntax

create_table_statement ::=
    CREATE [ OR REPLACE ] TABLE [schema_name].table_name AS query
    ;

schema_name ::= identifier

table_name ::= identifier

Parameters

Parameter	Description
OR REPLACE	Create a new table, and overwrite any existing table by the same name. Does not return an error if the table already exists. CREATE OR REPLACE does not check the table contents or structure, only the table name.
schema_name	The name of the schema in which to create the table.
table_name	The name of the table to create, which must be unique inside the schema.
query	A select query that returns data

Examples

Create a copy of an foreign table or view

CREATE TABLE users AS SELECT * FROM users_source;

Filtering

CREATE TABLE users_uk AS SELECT * FROM users WHERE country = 'United Kingdom';

Adding columns

CREATE TABLE users_uk_new AS SELECT GETDATE() as "Date",*,false as is_new FROM users_uk;

Creating a table from values

CREATE TABLE new_users
  AS VALUES(GETDATE(),'Richard','Foxworthy','1984-03-03',True)