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 |
---|---|
|
Create a new table, and overwrite any existing table by the same name. Does not return an error if the table already exists. |
|
The name of the schema in which to create the table. |
|
The name of the table to create, which must be unique inside the schema. |
|
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:
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.
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)