CREATE TABLE AS¶

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

Syntax¶

The following is the correct syntax for creating a table from the result of a select query:

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

schema_name ::= identifier

table_name ::= identifier

Parameters¶

The following parameters can be used when creating a table from the result of a select query:

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

Permissions¶

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

Examples¶

This section includes the following examples:

Creating a Copy of a Foreign Table or View
Filtering
Adding Columns
Creating a Table From Values

Warning

The SELECT statement decrypts information by default. When executing CREATE TABLE AS SELECT, encrypted information will appear as clear text in the newly created table.

Creating a Copy of a Foreign Table or View ¶

CREATE TABLE users AS SELECT * FROM users_source;

For more information, see CREATE FOREIGN TABLE.

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)