Help Center/ GaussDB/ M-Compatibility Developer Guide(Distributed)/ SQL Reference/ SQL Syntax/ SQL Statements/ S/ SELECT INTO
Updated on 2025-10-23 GMT+08:00
View PDF
Share

SELECT INTO

Description

SELECT INTO creates a table based on the query result and inserts data retrieved by the query into the new table.

Different from SELECT, data is not returned to the client. The columns of the new table have the same names and data types as the output columns of SELECT.

Syntax

[ WITH [ RECURSIVE ] with_query [, ...] ]
SELECT
    [ALL | DISTINCT | DISTINCTROW]
    [SQL_NO_CACHE]
    select_expr [, select_expr] ...
    INTO [ [ GLOBAL | LOCAL ] [ TEMPORARY ]  ] [ TABLE ] new_table
    [FROM from_item]
    [WHERE where_condition]
    [GROUP BY {col_name | expr | position}]
    [HAVING condition]
    [ORDER BY {col_name | expression | position}
      [ASC | DESC], ...]
    [LIMIT {[offset,] row_count | row_count OFFSET offset}]
    INTO [ [ GLOBAL | LOCAL ] [ TEMPORARY ]  ] [ TABLE ] new_table
    [ FOR READ ONLY | 
        [ {FOR { UPDATE | SHARE | } [ OF table_name [, ...] ] [ NOWAIT | WAIT N ]} [...] ] |
        LOCK IN SHARE MODE];

Parameters

  • new_table

    Specifies the name of the new table.

  • GLOBAL | LOCAL

    When creating a temporary table, you can specify GLOBAL or LOCAL before TEMP or TEMPORARY. If GLOBAL is specified, a global temporary table will be created for an M-compatible database. Otherwise, a local temporary table will be created.

  • TEMPORARY

    If TEMPORARY is specified, the created table is a temporary table. Temporary tables are classified into global temporary tables and local temporary tables. If GLOBAL is specified when creating a temporary table, the table is a global temporary table. Otherwise, the table is a local temporary table.

    The metadata of a global temporary table is visible to all sessions. After sessions end, the metadata still exists. The user data, indexes, and statistics between sessions are isolated. Each session can only view and modify the data committed by itself. Global temporary tables can be created using ON COMMIT PRESERVE ROWS or ON COMMIT DELETE ROWS. The former creates session-level tables, where user data is automatically cleared when a session ends; the latter creates transaction-level tables, where user data is automatically cleared when a commit or rollback operation is performed. If the ON COMMIT option is not specified during table creation, the session-based schema is used by default. Different from local temporary tables, you can specify a schema that does not start with pg_temp_ when creating a global temporary table.

    A local temporary table is visible only in the current session and is automatically dropped at the end of the session. Therefore, you can create and use the temporary tables in the current session as long as the database node that the session connects to is normal. Using DDL statements on temporary tables will cause a DDL failure error because temporary tables are created only in the current session. Therefore, you are advised not to perform operations on temporary tables using DDL statements. TEMP is equivalent to TEMPORARY.

    • Local temporary tables use independent schemas starting with pg_temp for each session to ensure that they are visible only to the current session. Therefore, you are advised not to manually delete schemas starting with pg_temp or pg_toast_temp during routine operations.
    • If TEMPORARY or TEMP is not specified when creating the table and the schema of the table is set to that starting with pg_temp_ in the current session, the table will be created as a temporary table.
    • If global temporary tables or indexes are being used by other sessions, do not perform ALTER or DROP on the tables or indexes.
    • The DDL of a global temporary table affects only the user data and indexes of the current session. For example, TRUNCATE, REINDEX, and ANALYZE are valid only for the current session.

For details about other SELECT INTO parameters, see Parameters in section "SELECT".

Examples

-- Create a schema.
m_db=# CREATE SCHEMA tpcds;

-- Create a table named tpcds.reason.
m_db=# CREATE TABLE tpcds.reason
(
  r_reason_sk      integer,
  r_reason_id      character(16),
  r_reason_desc    character(100)
);

-- Insert multiple records into the table.
m_db=# INSERT INTO tpcds.reason values(1,'AAAAAAAABAAAAAAA','reason 1'),(2,'AAAAAAAABAAAAAAA','reason 2'),(3,'AAAAAAAABAAAAAAA','reason 3'),(4,'AAAAAAAABAAAAAAA','reason 4'),(4,'AAAAAAAABAAAAAAA','reason 5'),(4,'AAAAAAAACAAAAAAA','reason 6'),(5,'AAAAAAAACAAAAAAA','reason 7');

-- Add the values of r_reason_sk that are less than 5 in the tpcds.reason table to a new table.
m_db=# SELECT * INTO tpcds.reason_t1 FROM tpcds.reason WHERE r_reason_sk < 5;
INSERT 0 6

-- Drop the tpcds.reason_t1 table.
m_db=# DROP TABLE tpcds.reason_t1;

-- Drop the table.
m_db=# DROP TABLE tpcds.reason;

-- Drop the schema.
m_db=# DROP SCHEMA tpcds;

Helpful Links

SELECT

Parent topic: S