1INSERT(7) PostgreSQL 9.2.24 Documentation INSERT(7)
2
6 INSERT - create new rows in a table
7
9 [ WITH [ RECURSIVE ] with_query [, ...] ]
10 INSERT INTO table_name [ ( column_name [, ...] ) ]
11 { DEFAULT VALUES | VALUES ( { expression | DEFAULT } [, ...] ) [, ...] | query }
12 [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]
13
15 INSERT inserts new rows into a table. One can insert one or more rows
16 specified by value expressions, or zero or more rows resulting from a
17 query.
18 The target column names can be listed in any order. If no list of
20 column names is given at all, the default is all the columns of the
21 table in their declared order; or the first N column names, if there
22 are only N columns supplied by the VALUES clause or query. The values
23 supplied by the VALUES clause or query are associated with the explicit
24 or implicit column list left-to-right.
25 Each column not present in the explicit or implicit column list will be
27 filled with a default value, either its declared default value or null
28 if there is none.
29 If the expression for any column is not of the correct data type,
31 automatic type conversion will be attempted.
32 The optional RETURNING clause causes INSERT to compute and return
34 value(s) based on each row actually inserted. This is primarily useful
35 for obtaining values that were supplied by defaults, such as a serial
36 sequence number. However, any expression using the table's columns is
37 allowed. The syntax of the RETURNING list is identical to that of the
38 output list of SELECT.
39 You must have INSERT privilege on a table in order to insert into it.
41 If a column list is specified, you only need INSERT privilege on the
42 listed columns. Use of the RETURNING clause requires SELECT privilege
43 on all columns mentioned in RETURNING. If you use the query clause to
44 insert rows from a query, you of course need to have SELECT privilege
45 on any table or column used in the query.
46
48 with_query
49 The WITH clause allows you to specify one or more subqueries that
50 can be referenced by name in the INSERT query. See Section 7.8,
51 “WITH Queries (Common Table Expressions)”, in the documentation and
52 SELECT(7) for details.
53 It is possible for the query (SELECT statement) to also contain a
55 WITH clause. In such a case both sets of with_query can be
56 referenced within the query, but the second one takes precedence
57 since it is more closely nested.
58 table_name
60 The name (optionally schema-qualified) of an existing table.
61 column_name
63 The name of a column in the table named by table_name. The column
64 name can be qualified with a subfield name or array subscript, if
65 needed. (Inserting into only some fields of a composite column
66 leaves the other fields null.)
67 DEFAULT VALUES
69 All columns will be filled with their default values.
70 expression
72 An expression or value to assign to the corresponding column.
73 DEFAULT
75 The corresponding column will be filled with its default value.
76 query
78 A query (SELECT statement) that supplies the rows to be inserted.
79 Refer to the SELECT(7) statement for a description of the syntax.
80 output_expression
82 An expression to be computed and returned by the INSERT command
83 after each row is inserted. The expression can use any column names
84 of the table named by table_name. Write * to return all columns of
85 the inserted row(s).
86 output_name
88 A name to use for a returned column.
89
91 On successful completion, an INSERT command returns a command tag of
92 the form
93 INSERT oid count
95 The count is the number of rows inserted. If count is exactly one, and
97 the target table has OIDs, then oid is the OID assigned to the inserted
98 row. Otherwise oid is zero.
99 If the INSERT command contains a RETURNING clause, the result will be
101 similar to that of a SELECT statement containing the columns and values
102 defined in the RETURNING list, computed over the row(s) inserted by the
103 command.
104
106 Insert a single row into table films:
107 INSERT INTO films VALUES
109 ('UA502', 'Bananas', 105, '1971-07-13', 'Comedy', '82 minutes');
110 In this example, the len column is omitted and therefore it will have
112 the default value:
113 INSERT INTO films (code, title, did, date_prod, kind)
115 VALUES ('T_601', 'Yojimbo', 106, '1961-06-16', 'Drama');
116 This example uses the DEFAULT clause for the date columns rather than
118 specifying a value:
119 INSERT INTO films VALUES
121 ('UA502', 'Bananas', 105, DEFAULT, 'Comedy', '82 minutes');
122 INSERT INTO films (code, title, did, date_prod, kind)
123 VALUES ('T_601', 'Yojimbo', 106, DEFAULT, 'Drama');
124 To insert a row consisting entirely of default values:
126 INSERT INTO films DEFAULT VALUES;
128 To insert multiple rows using the multirow VALUES syntax:
130 INSERT INTO films (code, title, did, date_prod, kind) VALUES
132 ('B6717', 'Tampopo', 110, '1985-02-10', 'Comedy'),
133 ('HG120', 'The Dinner Game', 140, DEFAULT, 'Comedy');
134 This example inserts some rows into table films from a table tmp_films
136 with the same column layout as films:
137 INSERT INTO films SELECT * FROM tmp_films WHERE date_prod < '2004-05-07';
139 This example inserts into array columns:
141 -- Create an empty 3x3 gameboard for noughts-and-crosses
143 INSERT INTO tictactoe (game, board[1:3][1:3])
144 VALUES (1, '{{" "," "," "},{" "," "," "},{" "," "," "}}');
145 -- The subscripts in the above example aren't really needed
146 INSERT INTO tictactoe (game, board)
147 VALUES (2, '{{X," "," "},{" ",O," "},{" ",X," "}}');
148 Insert a single row into table distributors, returning the sequence
150 number generated by the DEFAULT clause:
151 INSERT INTO distributors (did, dname) VALUES (DEFAULT, 'XYZ Widgets')
153 RETURNING did;
154 Increment the sales count of the salesperson who manages the account
156 for Acme Corporation, and record the whole updated row along with
157 current time in a log table:
158 WITH upd AS (
160 UPDATE employees SET sales_count = sales_count + 1 WHERE id =
161 (SELECT sales_person FROM accounts WHERE name = 'Acme Corporation')
162 RETURNING *
163 )
164 INSERT INTO employees_log SELECT *, current_timestamp FROM upd;
165
167 INSERT conforms to the SQL standard, except that the RETURNING clause
168 is a PostgreSQL extension, as is the ability to use WITH with INSERT.
169 Also, the case in which a column name list is omitted, but not all the
170 columns are filled from the VALUES clause or query, is disallowed by
171 the standard.
172 Possible limitations of the query clause are documented under
174 SELECT(7).
175PostgreSQL 9.2.24 2017-11-06 INSERT(7)