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)