Defines a new table from the results of a query.
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|QUICKLZ|RLE_TYPE|NONE}
compresslevel={1-19 | 1}
fillfactor={10-100}
[oids=FALSE]
NoteSupport for the QuickLZ compression algorithm is deprecated and will be removed in the next major release of VMware Greenplum.
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.
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.
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
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
, RLE_TYPE
, or QUICKLZ
1 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. QuickLZ and zlib are provided for backwards-compatibility. Zstd outperforms these compression types on usual workloads. The compresstype
option is valid only if appendoptimized=TRUE
.
NoteSupport for the QuickLZ compression algorithm is deprecated and will be removed in the next major release of VMware Greenplum.
Note1QuickLZ compression is available only in the commercial release of VMware Greenplum.
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, zlib, or QuickLZ 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.
For information about using table compression, see Choosing the Table Storage Model in the Greenplum Database Administrator Guide.
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. QuickLZ compression level can only be set to 1. 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.
WITH OIDS
or
oids=TRUE
to assign an OID system column.On large tables, such as those in a typical Greenplum Database 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 Greenplum Database 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 and will be removed in a future Greenplum release.
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.
SELECT
or
VALUES
query.
Used to declare the Greenplum Database 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 Greenplum Database segments. It cannot be used with partitioned tables or with tables that inhert from other tables.
The Greenplum Database 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. Greenplum Database follows these rules to create a table if a distribution policy is not specified.
off
, the table distribution policy is determined based on the command.on
, the table distribution policy is random.gp_create_table_random_default_distribution
. For information about the Postgres Planner and GPORCA, see
Querying Data in the
Greenplum Database Administrator Guide.
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.
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 >= '2007-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('2007-01-01');
CREATE TABLE AS
conforms to the SQL standard, with the following exceptions:
WITH [NO] DATA
clause; this is not currently implemented by Greenplum Database. The behavior provided by Greenplum Database is equivalent to the standard's WITH DATA
case. WITH NO DATA
can be simulated by appending LIMIT 0
to the query.CREATE TABLE
for details.WITH
clause is a Greenplum Database extension; neither storage parameters nor OIDs
are in the standard. The syntax for creating OID system columns is deprecated and will be removed in a future Greenplum release.TABLESPACE
clause is an extension.CREATE EXTERNAL TABLE, CREATE EXTERNAL TABLE, EXECUTE, SELECT, SELECT INTO, VALUES
Parent topic: SQL Commands