Product Documentation

Appearance

INSERT ​

Insert rows or column values into a table either by selecting them from other tables or by specifying a list of values to insert.

[ WITH name AS (subquery) [, ...] ]
INSERT INTO table_name [ AS alias ] [ ( column_name [, ...] ) ]
   { DEFAULT VALUES | VALUES ( { expression } [, ...] ) [, ...] | query }
WITH

See WITH Clause for syntax details.

table_name

Specify the name of a table in the current database.

Note: You cannot use INSERT commands on tables in remote databases; see Cross-Database Queries. You cannot use INSERT commands on tables in hot-standby databases; see HOT_STANDBY and READONLY Modes.

Warning: Running EXPLAIN ANALYZE on an INSERT, UPDATE, or DELETE statement modifies the target table, without warning. To avoid this problem, you can run the EXPLAIN ANALYZE statement inside a transaction, then roll it back if needed. See EXPLAIN ANALYZE for a DELETE.

DEFAULT VALUES

Insert all columns with default values if they are defined; otherwise, insert NULL values.

VALUES(expression [,…])

Insert a list of specific values into the table. You do not have to specify values for all columns.

To insert multiple rows, use a comma delimiter between each list of expressions. Do not repeat the VALUES keyword. All VALUES lists for a multiple-row INSERT statement must contain the same number of values.

Note: You cannot insert multiple rows at a time if the table contains encrypted columns.

VALUES (DEFAULT [,…])

Insert the specified columns with default values if they are defined; otherwise, insert NULL values.

Note: You can define WLM rules for INSERT INTO...SELECT statements but not for INSERT INTO...VALUES statements. INSERT INTO...VALUES operations are not executed by the backend database.

query

Insert the results of any valid query into the named table. Use a SELECT statement.

For example, insert a set of values into the season table:

premdb=# insert into season 
values(26, '2017-2018', 20, null);
INSERT 0 1

Insert multiple rows into the season table in one statement:

premdb=# insert into season 
values(26, '2017-2018', 20, null),(27,'2018-2019',21,null);
INSERT 0 2

For example, load the hometeam table by selecting from the team table:

premdb=# insert into hometeam 
select htid,name from team;
INSERT 0 50

Insert a row of NULL values into the season table, which has no DEFAULT values assigned to its columns:

premdb=# insert into season default values;
INSERT 0 1

Select data from an external table and insert it into a regular database table. You do not have to declare the column list for the file in this case; it is optional. The USING clause is required. See External Tables and USING Options.

premdb=# insert into season 
select * from external 'ext_db/season.csv' 
using(format csv);
INSERT 0 25

The external table in this statement uses the schema of the target table (season). The columns in the external data file must be in the same order as they are in the target table, and every column in the target table must also exist in the external file.

Note: You cannot use INSERT commands on hot-standby databases.

Query Processing for INSERT Commands ​

INSERT INTO...VALUES and INSERT INTO...SELECT statements are processed differently. You can see the difference by using the EXPLAIN command. For example:

premdb=# explain insert into team values(200,100);
                         QUERY PLAN                           
---------------------------------------------------------------
 Insert on team (on manager)  (cost=0.00..0.00 rows=1 width=0)
   ->  Result (on manager)  (cost=0.00..0.00 rows=1 width=0)
(2 rows)

premdb=# explain insert into team select 200,htid from hometeam;
                   QUERY PLAN                     
---------------------------------------------------
 id   rows_planned   workers   node   
  1             50    single   INSERT INTO team   
  3             50       all   SCAN hometeam   
 Database: premdb
 Version: 4.1.0-23999
 Hostname: yb100
 
(7 rows)

The first INSERT updates the row store ("on manager") and the rows will later be flushed to the column store. The second INSERT updates the column store directly ("workers"). See also Managing the Row Store.

Parent topic:SQL Commands