English

SELECT INTO

Define a new table based on the query results.

Synopsis

[ WITH [ RECURSIVE ] with_query [, ...] ]
SELECT [ALL | DISTINCT [ON ( expression [, ...] )]]
    * | expression [AS output_name] [, ...]
    INTO [TEMPORARY | TEMP | UNLOGGED ] [TABLE] new_table
    [FROM from_item [, ...]]
    [WHERE condition]
    [GROUP BY expression [, ...]]
    [HAVING condition [, ...]]
    [{UNION | INTERSECT | EXCEPT} [ALL | DISTINCT ] select]
    [ORDER BY expression [ASC | DESC | USING operator] [NULLS {FIRST | LAST}] [, ...]]
    [LIMIT {count | ALL}]
    [OFFSET start [ ROW | ROWS ] ]
    [FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ONLY ]
    [FOR {UPDATE | SHARE} [OF table_name [, ...]] [NOWAIT] 
    [...]]

Description

SELECT INTO creates a new table and fills the table with the data calculated by the query. Data is not returned to the client like a regular SELECT. The columns of the new table have the name and data type associated with the output column of SELECT.

Parameters

SELECT Most parameters of INTO are the same as SELECT.

TEMPORARY

TEMP

If specified, the table is created as a temporary table.

UNLOGGED

If specified, the table is created as not counted to the log table. Write data that does not count to the log table is not written to the write-pre-(WAL) log, which makes them much faster than normal tables. However, content that does not count into the log table is not copied to the mirror instance. Similarly, not counting in the log table is not crash-safe. After the segment instance crashes or closes abnormally, the data not counted in the log table on the segment will be truncated. All indexes created on the log table are automatically delogged.

new_table

The name of the table to be created (can be modified with schema).

Example

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

SELECT * INTO films_recent FROM films WHERE date_prod >= '2016-01-01';

Compatibility

The SQL standard uses SELECT INTO to select values into scalar variables of the host program instead of creating a new table. The usage of SELECT INTO in YMatrix databases for table creation is historical. For this purpose, it is best to use CREATE TABLE AS in new applications.

← Previous

SELECT

SET