CREATE TABLE AS

Define a new table based on the query results.

Summary

CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE table_name
        [ (column_name [, ...] ) ]
        [ USING MARS2 ]
        [ WITH ( storage_parameter [= value] [, ... ] ) | WITHOUT OIDS ]
        [ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
        [ TABLESPACE tablespace_name ]
        AS query
        [ WITH [ NO ] DATA ]
        [ DISTRIBUTED BY (column [, ... ] ) | DISTRIBUTED RANDOMLY | DISTRIBUTED REPLICATED ]

where storage_parameter is:

   appendoptimized={TRUE|FALSE}
   blocksize={8192-2097152}
   orientation={COLUMN|ROW}
   compresstype={ZLIB|ZSTD|QUICKLZ|RLE_TYPE|NONE}
   compresslevel={1-19 | 1}
   fillfactor={10-100}
   [oids=FALSE]

describe

CREATE TABLE AS Create a table and populate it with the data calculated by the SELECT command. Table columns have the name and data type associated with the output column of SELECT, but you can override the column names by providing an explicit list of the new column names.

CREATE TABLE AS Create a new table and performs a value on the query only once to fill the new table once. The new table will not track subsequent changes to the query source table.

Parameters

GLOBAL | LOCAL

Ignore compatibility. These keywords are deprecated.

TEMPORARY | TEMP

If specified, create the new table as a temporary table. Temporary tables are automatically deleted at the end of the session or at the end of the current transaction (see ON COMMIT). When temporary tables exist, existing permanent tables with the same name are not visible in the current session unless they are referenced using a schema-qualified name. All indexes created on temporary tables will also automatically become temporary indexes.

UNLOGGED

If specified, the table will be created as an unrecorded table. Write data to unlogged tables does not write to write-pre-(WAL) logs, which makes them much faster than normal tables. However, the contents of the unlogged table are not copied to the Mirror instance. Again, unrecorded tables are not crash-safe. After the Segment instance crashes or closes abnormally, the data of the unrecorded table on the Segment will be truncated. All indexes created on the unrecorded table will also automatically become unrecorded indexes.

table_name

The name of the new table to be created (can be specified by the schema).

column_name

Column name in the new table. If column names are not provided, they are taken from the output column names of the query.

WITH (storage_parameter=value)

The WITH clause can be used to set storage options for a table or its index. Note that you can also set different storage parameters on a specific partition or subpartition by declaring the WITH clause in the partition specification. The following storage options are available:
- appendoptimized — Set to TRUE to create a table as an appended table. If FALSE or not declared, the table is created as a regular heap storage table.
- blocksize — Set to the size of each block in the table in bytes. blocksize must be between 8192 and 2097152 bytes and is a multiple of 8192. The default value is 32768. This parameter is only effective when appendoptimized = TRUE.
- orientation — Set to column for column storage, or set to row (default) for row storage. This option is only valid if appendoptimized = TRUE. Heap storage tables can only be row-oriented.
- compresstype — Set to ZLIB (default), ZSTD, or QUICKLZ1 to specify the compression type used. Value NONE Disable compression. ZSTD provides speed or good compression rates, which can be adjusted with the compresslevel option. QuickLZ and ZLIB are provided for backward compatibility. On a normal workload, ZSTD performs better than these compression types. The compresstype option is only valid if appendoptimized = TRUE.
- compresslevel — For ZSTD compression of additional optimization tables, set it to an integer value between 1 (fastest compression) and 19 (maximum compression ratio). For ZLIB compression, the effective range is 1 to 9. QuickLZ compression level can only be set to 1. If not declared, the default value is 1. The compresslevel option is only valid when appendoptimized = TRUE.
- fillfactor — For more information about this index storing parameters, see CREATE INDEX.
- oids=FALSE — This is the default value, no oid is assigned within each data line. YMatrix strongly recommends that you do not enable OIDS when creating tables. On large tables (such as tables in a typical YMatrix database system), using OIDs on table rows can result in a 32-bit OID counter. Once the counter is wound back, it can no longer be considered that OIDs are unique, which not only makes them useless to user applications, but also causes problems in the catalog table of the YMatrix database system. Additionally, excluding OIDs from a table reduces the space required for each row to be stored on disk by 4 bytes per row, resulting in a slight improvement in performance. OIDS is not allowed for column-oriented tables. ON COMMIT
You can use ON COMMIT to control the behavior of temporary tables at the end of a transaction block. These three options are:
PRESERVE ROWS — No special actions are taken at the end of the transaction of the temporary table. This is the default behavior.
DELETE ROWS — All rows in the temporary table will be deleted at the end of each transaction block. Essentially, TRUNCATE is automatically performed once per commit.
DROP — The temporary table will be deleted at the end of the current transaction block.

TABLESPACE tablespace_name

The tablespace_name parameter is the name of the tablespace in which the new table is to be created. If not specified, the default tablespace of the database is used.

AS query

SELECT, TABLE or VALUES command, or EXECUTE command to run prepared SELECT or VALUES query.

DISTRIBUTED BY ({column [opclass]}, [ ... ] )

DISTRIBUTED RANDOMLY

DISTRIBUTED REPLICATED

YMatrix database distribution policy for declaring tables. DISTRIBUTED BY Distributes using hashedged columns with one or more columns declared as distribution keys. To get the most uniform data allocation, the distribution key should be the primary key or unique column (or a set of columns) of the table. If you cannot do this, you can choose DISTRIBUTED RANDOMLY, which sends data polling to the Segment instance.
DISTRIBUTED REPLICATED Copy all rows in the table to all YMatrix database segments. It cannot be used with partitioned tables or tables inherited from other tables.
If the DISTRIBUTED BY clause is not specified when creating the table, the YMatrix database server configuration parameter gp_create_table_random_default_distribution controls the default table distribution policy. If a distribution policy is not specified, the YMatrix database will follow the following rules to create the table:
- If the Postgres query optimizer creates a table and the value of this parameter is off, the table allocation policy is determined based on the command.
- If the Postgres query optimizer creates a table and the value of the parameter is on, the table allocation policy is random.
- If GPORCA creates a table, the table allocation policy is random. The parameter value is invalid.

Note

This command is functionally similar to SELECT INTO, but it is preferred because it is unlikely to be confused with other usages of the SELECT INTO syntax. Additionally, CREATE TABLE AS provides a superset of the features provided by SELECT INTO.

CREATE TABLE AS can be used to quickly load data from external table data sources. See CREATE EXTERNAL TABLE.

Example

Create a new table films_recent that contains only the latest entries in the table films:

CREATE TABLE films_recent AS SELECT * FROM films WHERE 
date_prod >= '2007-01-01';

Use precompiled statements to create a new temporary table films_recent that contains only the latest entries in the table films. The new table has an OID and will be deleted on commit:

PREPARE recentfilms(date) AS SELECT * FROM films WHERE 
date_prod > $1;
CREATE TEMP TABLE films_recent ON COMMIT DROP AS 
EXECUTE recentfilms('2007-01-01');

compatibility

CREATE TABLE AS complies with SQL standards, except for the following:

This standard requires brackets to be added on both sides of the subquery clause; in the YMatrix database, these brackets are optional.
This standard defines the WITH [NO] DATA clause; this feature is not currently implemented in the YMatrix database. The YMatrix database provides behavior equivalent to the standard WITH DATA situation. WITH NO DATA can be simulated by appending LIMIT 0 to the query.
The YMatrix database handles temporary tables differently from the standard ones. For more information, see CREATE TABLE.
The WITH clause is a YMatrix database extension. Neither the storage parameters nor OIDs are in the standard. The syntax for creating an OID system column has been deprecated.
The YMatrix database concept for tablespaces is not part of this standard. The TABLESPACE clause is an extension.