Product Documentation

Appearance

All platforms

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.

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.

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 frontend queries that do not require a cluster and 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

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.