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


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


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

schema_name ::= identifier

table_name ::= identifier


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


Create a copy of an foreign table or view

CREATE TABLE users AS SELECT * FROM users_source;


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)