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 useINSERT
commands on tables in hot-standby databases; see HOT_STANDBY and READONLY Modes.Warning: RunningEXPLAIN ANALYZE
on anINSERT
,UPDATE
, orDELETE
statement modifies the target table, without warning. To avoid this problem, you can run theEXPLAIN 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. AllVALUES
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 forINSERT INTO...SELECT
statements but not forINSERT 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.
season
table:premdb=# insert into season
values(26, '2017-2018', 20, null);
INSERT 0 1
season
table in one
statement:premdb=# insert into season
values(26, '2017-2018', 20, null),(27,'2018-2019',21,null);
INSERT 0 2
hometeam
table by selecting from the
team
table:premdb=# insert into hometeam
select htid,name from team;
INSERT 0 50
NULL
values into the season
table, which
has no DEFAULT
values assigned to its columns:
premdb=# insert into season default values;
INSERT 0 1
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.
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.