.\\" auto-generated by docbook2man-spec $Revision: 1.25 $
.TH "INSERT" "7" "2002-11-22" "SQL - Language Statements" "SQL Commands"
.SH NAME
INSERT \- create new rows in a table
.SH SYNOPSIS
.sp
.nf
INSERT INTO \fItable\fR [ ( \fIcolumn\fR [, ...] ) ]
    { DEFAULT VALUES | VALUES ( { \fIexpression\fR | DEFAULT } [, ...] ) | SELECT \fIquery\fR }
  
.sp
.fi
.SS "INPUTS"
.PP
.TP
\fB\fItable\fB\fR
The name (optionally schema-qualified) of an existing table.
.TP
\fB\fIcolumn\fB\fR
The name of a column in \fItable\fR.
.TP
\fBDEFAULT VALUES\fR
All columns will be filled by null values or by values specified
when the table was created using DEFAULT clauses.
.TP
\fB\fIexpression\fB\fR
A valid expression or value to assign to \fIcolumn\fR.
.TP
\fB\fIDEFAULT\fB\fR
This column will be filled in by the column DEFAULT clause, or NULL if
a default is not available.
.TP
\fB\fIquery\fB\fR
A valid query. Refer to the SELECT statement for a further description
of valid arguments.
.PP
.SS "OUTPUTS"
.PP
.TP
\fBINSERT \fIoid\fB 1\fR
Message returned if only one row was inserted.
\fIoid\fR
is the numeric OID of the inserted row.
.TP
\fBINSERT 0 \fI#\fB\fR
Message returned if more than one rows were inserted.
\fI#\fR
is the number of rows inserted.
.PP
.SH "DESCRIPTION"
.PP
\fBINSERT\fR allows one to insert new rows into a
table. One can insert
a single row at a time or several rows as a result of a query.
The columns in the target list may be listed in any order.
.PP
Each column not present in the target list will be inserted
using a default value, either a declared DEFAULT value
or NULL. PostgreSQL will reject the new
column if a NULL is inserted into a column declared NOT NULL.
.PP
If the expression for each column
is not of the correct data type, automatic type coercion will be
attempted.
.PP
You must have insert privilege to a table in order to append
to it, as well as select privilege on any table specified
in a WHERE clause.
.SH "USAGE"
.PP
Insert a single row into table films:
.sp
.nf
INSERT INTO films VALUES
    ('UA502','Bananas',105,'1971-07-13','Comedy',INTERVAL '82 minute');
   
.sp
.fi
.PP
In this second example the last column len is
omitted and therefore it will have the default value of NULL:
.sp
.nf
INSERT INTO films (code, title, did, date_prod, kind)
    VALUES ('T_601', 'Yojimbo', 106, DATE '1961-06-16', 'Drama');
   
.sp
.fi
.PP
In the third example, we use the DEFAULT values for the date columns
rather than specifying an entry.
.sp
.nf
INSERT INTO films VALUES
    ('UA502','Bananas',105,DEFAULT,'Comedy',INTERVAL '82 minute');
INSERT INTO films (code, title, did, date_prod, kind)
    VALUES ('T_601', 'Yojimbo', 106, DEFAULT, 'Drama');
   
.sp
.fi
.PP
Insert a single row into table distributors; note that
only column name is specified, so the omitted
column did will be assigned its default value:
.sp
.nf
INSERT INTO distributors (name) VALUES ('British Lion');
   
.sp
.fi
.PP
Insert several rows into table films from table tmp:
.sp
.nf
INSERT INTO films SELECT * FROM tmp;
   
.sp
.fi
.PP
Insert into arrays (refer to the
\fIPostgreSQL User's Guide\fR for further
information about arrays):
.sp
.nf
-- Create an empty 3x3 gameboard for noughts-and-crosses
-- (all of these queries create the same board attribute)
INSERT INTO tictactoe (game, board[1:3][1:3])
    VALUES (1,'{{"","",""},{},{"",""}}');
INSERT INTO tictactoe (game, board[3][3])
    VALUES (2,'{}');
INSERT INTO tictactoe (game, board)
    VALUES (3,'{{,,},{,,},{,,}}');
   
.sp
.fi
.SH "COMPATIBILITY"
.SS "SQL92"
.PP
\fBINSERT\fR is fully compatible with SQL92.
Possible limitations in features of the
\fIquery\fR
clause are documented for
SELECT [\fBselect\fR(7)].