Open search page
Hello, I’m DocuDroid!
Open Ask AI page
Submitting feedback
Thank you for rating our AI Search!
We would be grateful if you could share your thoughts so we can improve our AI Search for you and other readers.
GitHub

CREATE TABLE AS

Defines a new table from the results of a query.

Synopsis

CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE <table_name>
        [ (<column_name> [, ...] ) ]
        [ 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|RLE_TYPE|NONE}
compresslevel={1-19}
fillfactor={10-100}
[oids=FALSE]

Description

CREATE TABLE AS creates a table and fills it with data computed by a SELECT command. The table columns have the names and data types associated with the output columns of the SELECT, however you can override the column names by giving an explicit list of new column names.

CREATE TABLE AS creates a new table and evaluates the query just once to fill the new table initially. The new table will not track subsequent changes to the source tables of the query.

Parameters

Parameter Description

GLOBAL | LOCAL

Ignored for compatibility. These keywords are deprecated; refer to CREATE TABLE for details

TEMPORARY | TEMP

If specified, the new table is created as a temporary table. Temporary tables are automatically dropped at the end of a session, or optionally at the end of the current transaction (see ON COMMIT). Existing permanent tables with the same name are not visible to the current session while the temporary table exists, unless they are referenced with schema-qualified names. Any indexes created on a temporary table are automatically temporary as well

UNLOGGED

If specified, the table is created as an unlogged table. Data written to unlogged tables is not written to the write-ahead (WAL) log, which makes them considerably faster than ordinary tables. However, the contents of an unlogged table are not replicated to mirror segment instances. Also an unlogged table is not crash-safe. After a segment instance crash or unclean shutdown, the data for the unlogged table on that segment is truncated. Any indexes created on an unlogged table are automatically unlogged as well

table_name

The name (optionally schema-qualified) of the new table to be created

column_name

The name of a column 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 the table or its indexes. Note that you can also set different storage parameters on a particular partition or subpartition by declaring the WITH clause in the partition specification. The following storage options are available:

  • appendoptimized — set to TRUE to create the table as an append-optimized table. If FALSE or not declared, the table will be created as a regular heap-storage table.

  • blocksize — set to the size, in bytes, for each block in a table. The blocksize value must be between 8192 and 2097152 bytes, and be a multiple of 8192. The default is 32768. The blocksize option is valid only if appendoptimized=TRUE.

  • orientation — set to column for column-oriented storage, or row (the default) for row-oriented storage. This option is only valid if appendoptimized=TRUE. Heap-storage tables can only be row-oriented.

  • compresstype — set to ZLIB (the default), ZSTD or RLE_TYPE to specify the type of compression used. The value NONE deactivates compression. Zstd provides for both speed or a good compression ratio, tunable with the compresslevel option. Zlib is provided for backwards-compatibility. Zstd outperforms these compression types on usual workloads. The compresstype option is valid only if appendoptimized=TRUE.

    The value RLE_TYPE, which is supported only if orientation=column is specified, enables the run-length encoding (RLE) compression algorithm. RLE compresses data better than the Zstd or zlib compression algorithms when the same data value occurs in many consecutive rows.

    For columns of type BIGINT, INTEGER, DATE, TIME, or TIMESTAMP, delta compression is also applied if the compresstype option is set to RLE_TYPE compression. The delta compression algorithm is based on the delta between column values in consecutive rows and is designed to improve compression when data is loaded in sorted order or the compression is applied to column data that is in sorted order.

  • compresslevel — for Zstd compression of append-optimized tables, set to an integer value from 1 (fastest compression) to 19 (highest compression ratio). For zlib compression, the valid range is from 1 to 9. If not declared, the default is 1. The compresslevel option is valid only if appendoptimized=TRUE.

  • fillfactor — see CREATE INDEX for more information about this index storage parameter.

  • oids=FALSE — this setting is the default, and it ensures that rows do not have object identifiers assigned to them. Greengage DB does not support using WITH OIDS or oids=TRUE to assign an OID system column. On large tables, such as those in a typical Greengage DB system, using OIDs for table rows can cause wrap-around of the 32-bit OID counter. Once the counter wraps around, OIDs can no longer be assumed to be unique, which not only makes them useless to user applications, but can also cause problems in the Greengage DB system catalog tables. In addition, excluding OIDs from a table reduces the space required to store the table on disk by 4 bytes per row, slightly improving performance. You cannot create OIDS on a partitioned or column-oriented table (an error is displayed). This syntax is deprecated.

ON COMMIT

The behavior of temporary tables at the end of a transaction block can be controlled using ON COMMIT. The three options are:

  • PRESERVE ROWS — no special action is taken at the ends of transactions for temporary tables. This is the default behavior.

  • DELETE ROWS — all rows in the temporary table will be deleted at the end of each transaction block. Essentially, an automatic TRUNCATE is done at each commit.

  • DROP — the temporary table will be dropped 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 database’s default tablespace is used, or temp_tablespaces if the table is temporary

AS query

A SELECT, TABLE, or VALUES command, or an EXECUTE command that runs a prepared SELECT or VALUES query

DISTRIBUTED BY ({<column> [<opclass>]}, [ …​ ] )

DISTRIBUTED RANDOMLY

DISTRIBUTED REPLICATED

Used to declare the Greengage DB distribution policy for the table. DISTRIBUTED BY uses hash distribution with one or more columns declared as the distribution key. For the most even data distribution, the distribution key should be the primary key of the table or a unique column (or set of columns). If that is not possible, then you may choose DISTRIBUTED RANDOMLY, which will send the data round-robin to the segment instances.

DISTRIBUTED REPLICATED replicates all rows in the table to all Greengage DB segments. It cannot be used with partitioned tables or with tables that inherit from other tables.

The Greengage DB server configuration parameter gp_create_table_random_default_distribution controls the default table distribution policy if the DISTRIBUTED BY clause is not specified when you create a table. Greengage DB follows these rules to create a table if a distribution policy is not specified:

  • If the Postgres planner creates the table, and the value of the parameter is off, the table distribution policy is determined based on the command.

  • If the Postgres planner creates the table, and the value of the parameter is on, the table distribution policy is random.

  • If GPORCA creates the table, the table distribution policy is random. The parameter value has no effect.

Notes

This command is functionally similar to SELECT INTO, but it is preferred since it is less likely to be confused with other uses of the SELECT INTO syntax. Furthermore, CREATE TABLE AS offers a superset of the functionality offered by SELECT INTO.

CREATE TABLE AS can be used for fast data loading from external table data sources. See CREATE EXTERNAL TABLE.

Examples

Create a new table films_recent consisting of only recent entries from the table films:

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

Create a new temporary table films_recent, consisting of only recent entries from the table films, using a prepared statement. The new table will be dropped at commit:

PREPARE recentfilms(date) AS SELECT *
                             FROM films
                             WHERE date_prod > $1;

CREATE TEMP TABLE films_recent ON COMMIT DROP AS
    EXECUTE recentfilms
(
    '2025-01-01'
);

Compatibility

CREATE TABLE AS conforms to the SQL standard, with the following exceptions:

  • The standard requires parentheses around the subquery clause; in Greengage DB, these parentheses are optional.

  • In the standard, the WITH [NO] DATA clause is required; in Greengage DB, it is optional.

  • Greengage DB handles temporary tables differently from the standard; see CREATE TABLE for details.

  • The WITH clause is a Greengage DB extension; neither storage parameters nor OIDs are in the standard. The syntax for creating OID system columns is deprecated.

  • The Greengage DB concept of tablespaces is not part of the standard. The TABLESPACE clause is an extension.

See also

Previous
CREATE TABLESPACE
Next
CREATE TEXT SEARCH CONFIGURATION