1 <REFENTRY ID="SQL-INSERT">
6 <REFMISCINFO>SQL - Language Statements</REFMISCINFO>
13 Inserts new rows into a table
17 <DATE>1998-09-02</DATE>
20 INSERT INTO <REPLACEABLE CLASS="PARAMETER">table</REPLACEABLE> [ ( <REPLACEABLE CLASS="PARAMETER">column</REPLACEABLE> [, ...] ) ]
21 { VALUES ( <REPLACEABLE CLASS="PARAMETER">expression</REPLACEABLE> [, ...] ) | SELECT <REPLACEABLE CLASS="PARAMETER">query</REPLACEABLE> }
24 <REFSECT2 ID="R2-SQL-INSERT-1">
26 <DATE>1998-04-15</DATE>
36 <REPLACEABLE CLASS="PARAMETER">table</REPLACEABLE>
40 The name of an existing table.
44 <REPLACEABLE CLASS="PARAMETER">column</REPLACEABLE>
48 The name of a column in <REPLACEABLE CLASS="PARAMETER">table</REPLACEABLE>.
52 <REPLACEABLE CLASS="PARAMETER">expression</REPLACEABLE>
56 A valid expression or value to assign to <REPLACEABLE CLASS="PARAMETER">column</REPLACEABLE>.
60 <REPLACEABLE CLASS="PARAMETER">query</REPLACEABLE>
64 A valid query. Refer to the SELECT statement for a further description
71 <REFSECT2 ID="R2-SQL-INSERT-2">
73 <DATE>1998-04-15</DATE>
83 INSERT <ReturnValue>oid</ReturnValue> 1
87 Message returned if only one row was inserted.
88 <ReturnValue>oid</ReturnValue> is the row identifier.
92 INSERT 0 <ReturnValue>#</ReturnValue>
96 Message returned if more than one rows were inserted.
97 <ReturnValue>#</ReturnValue> is the number of rows inserted.
104 <REFSECT1 ID="R1-SQL-INSERT-1">
106 <DATE>1998-09-02</DATE>
112 INSERT allows one to insert new rows into a table. One can insert
113 a single row at time or several rows as a result of a query.
114 The columns in the target list may be listed in any order.
115 In every column not present in the target list will be inserted
116 the default value, if column has not a declared default value
117 it will be assumed as NULL. If the expression for each column
118 is not of the correct data type, automatic type coercion will be
122 You must have insert privilege to a table in order to append
123 to it, as well as select privilege on any table specified
126 <REFSECT1 ID="R1-SQL-INSERT-2">
132 --Insert a single row into table films;
133 --(in the second example the column date_prod is omitted
134 --therefore will be stored in it a default value of NULL):
136 INSERT INTO films VALUES
137 ('UA502','Bananas',105,'1971-07-13','Comedy',INTERVAL '82 minute');
139 INSERT INTO films (code, title, did, date_prod, kind)
140 VALUES ('T_601', 'Yojimbo', 106, DATE '1961-06-16', 'Drama');
144 --Insert a single row into table distributors, note that
145 --only column "name" is specified, to the non specified
146 --column "did" will be assigned its default value:
148 INSERT INTO distributors (name) VALUES ('British Lion');
152 --Insert several rows into table films from table tmp:
159 --Insert into arrays:
160 --Create an empty 3x3 gameboard for noughts-and-crosses
161 --(all of these queries create the same board attribute)
162 --(Refer to PostgreSQL User's Guide chapter 7 for further
163 --information about arrays).
165 INSERT INTO tictactoe (game, board[1:3][1:3])
166 VALUES (1,'{{"","",""},{},{"",""}}');
167 INSERT INTO tictactoe (game, board[3][3])
169 INSERT INTO tictactoe (game, board)
170 VALUES (3,'{{,,},{,,},{,,}}');
175 <REFSECT1 ID="R1-SQL-INSERT-3">
182 <REFSECT2 ID="R2-SQL-INSERT-4">
184 <DATE>1998-04-15</DATE>
190 The INSERT statement is fully compatible with <acronym>SQL92</acronym>.
191 Possible limitations in features of the
192 <REPLACEABLE CLASS="PARAMETER">query</REPLACEABLE>
193 clause are documented for the SELECT statement.
198 <REPLACEABLE CLASS="PARAMETER">
200 <ReturnValue></ReturnValue>