1INSERT() SQL Commands INSERT()
2
6 INSERT - create new rows in a table
7
10 INSERT INTO table [ ( column [, ...] ) ]
11 { DEFAULT VALUES | VALUES ( { expression | DEFAULT } [, ...] ) [, ...] | query }
12 [ RETURNING * | output_expression [ AS output_name ] [, ...] ]
13
16 INSERT inserts new rows into a table. One can insert one or more rows
17 specified by value expressions, or zero or more rows resulting from a
18 query.
19 The target column names may be listed in any order. If no list of col‐
21 umn names is given at all, the default is all the columns of the table
22 in their declared order; or the first N column names, if there are only
23 N columns supplied by the VALUES clause or query. The values supplied
24 by the VALUES clause or query are associated with the explicit or
25 implicit column list left-to-right.
26 Each column not present in the explicit or implicit column list will be
28 filled with a default value, either its declared default value or null
29 if there is none.
30 If the expression for any column is not of the correct data type, auto‐
32 matic type conversion will be attempted.
33 The optional RETURNING clause causes INSERT to compute and return
35 value(s) based on each row actually inserted. This is primarily useful
36 for obtaining values that were supplied by defaults, such as a serial
37 sequence number. However, any expression using the table's columns is
38 allowed. The syntax of the RETURNING list is identical to that of the
39 output list of SELECT.
40 You must have INSERT privilege on a table in order to insert into it,
42 and SELECT privilege on it to use RETURNING. If you use the query
43 clause to insert rows from a query, you also need to have SELECT privi‐
44 lege on any table used in the query.
45
47 table The name (optionally schema-qualified) of an existing table.
48 column The name of a column in table. The column name can be qualified
50 with a subfield name or array subscript, if needed. (Inserting
51 into only some fields of a composite column leaves the other
52 fields null.)
53 DEFAULT VALUES
55 All columns will be filled with their default values.
56 expression
58 An expression or value to assign to the corresponding column.
59 DEFAULT
61 The corresponding column will be filled with its default value.
62 query A query (SELECT statement) that supplies the rows to be
64 inserted. Refer to the SELECT [select(7)] statement for a
65 description of the syntax.
66 output_expression
68 An expression to be computed and returned by the INSERT command
69 after each row is inserted. The expression may use any column
70 names of the table. Write * to return all columns of the
71 inserted row(s).
72 output_name
74 A name to use for a returned column.
75
77 On successful completion, an INSERT command returns a command tag of
78 the form
79 INSERT oid count
81 The count is the number of rows inserted. If count is exactly one, and
83 the target table has OIDs, then oid is the OID assigned to the inserted
84 row. Otherwise oid is zero.
85 If the INSERT command contains a RETURNING clause, the result will be
87 similar to that of a SELECT statement containing the columns and values
88 defined in the RETURNING list, computed over the row(s) inserted by the
89 command.
90
92 Insert a single row into table films:
93 INSERT INTO films VALUES
95 ('UA502', 'Bananas', 105, '1971-07-13', 'Comedy', '82 minutes');
96 In this example, the len column is omitted and therefore it will have
99 the default value:
100 INSERT INTO films (code, title, did, date_prod, kind)
102 VALUES ('T_601', 'Yojimbo', 106, '1961-06-16', 'Drama');
103 This example uses the DEFAULT clause for the date columns rather than
106 specifying a value:
107 INSERT INTO films VALUES
109 ('UA502', 'Bananas', 105, DEFAULT, 'Comedy', '82 minutes');
110 INSERT INTO films (code, title, did, date_prod, kind)
111 VALUES ('T_601', 'Yojimbo', 106, DEFAULT, 'Drama');
112 To insert a row consisting entirely of default values:
115 INSERT INTO films DEFAULT VALUES;
117 To insert multiple rows using the multirow VALUES syntax:
120 INSERT INTO films (code, title, did, date_prod, kind) VALUES
122 ('B6717', 'Tampopo', 110, '1985-02-10', 'Comedy'),
123 ('HG120', 'The Dinner Game', 140, DEFAULT, 'Comedy');
124 This example inserts some rows into table films from a table tmp_films
127 with the same column layout as films:
128 INSERT INTO films SELECT * FROM tmp_films WHERE date_prod < '2004-05-07';
130 This example inserts into array columns:
133 -- Create an empty 3x3 gameboard for noughts-and-crosses
135 INSERT INTO tictactoe (game, board[1:3][1:3])
136 VALUES (1, '{{" "," "," "},{" "," "," "},{" "," "," "}}');
137 -- The subscripts in the above example aren't really needed
138 INSERT INTO tictactoe (game, board)
139 VALUES (2, '{{X," "," "},{" ",O," "},{" ",X," "}}');
140 Insert a single row into table distributors, returning the sequence
143 number generated by the DEFAULT clause:
144 INSERT INTO distributors (did, dname) VALUES (DEFAULT, 'XYZ Widgets')
146 RETURNING did;
147
150 INSERT conforms to the SQL standard, except that the RETURNING clause
151 is a PostgreSQL extension. Also, the case in which a column name list
152 is omitted, but not all the columns are filled from the VALUES clause
153 or query, is disallowed by the standard.
154 Possible limitations of the query clause are documented under SELECT
156 [select(7)].
157SQL - Language Statements 2008-06-08 INSERT()