File: sql-insert.html

package info (click to toggle)
mpsql 2.1-2
links: PTS
area: non-free
in suites: potato
size: 3,528 kB
ctags: 4,886
sloc: ansic: 35,184; makefile: 3,761; sh: 44
file content (379 lines) | stat: -rw-r--r-- 5,717 bytes
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML
><HEAD
><TITLE
>INSERT</TITLE
><META
NAME="GENERATOR"
CONTENT="Modular DocBook HTML Stylesheet version 1.19"><LINK
REL="HOME"
TITLE="PostgreSQL User's Guide"
HREF="user.html"><LINK
REL="UP"
TITLE="SQL Commands"
HREF="sql-commands.html"><LINK
REL="PREVIOUS"
TITLE="GRANT"
HREF="sql-grant.html"><LINK
REL="NEXT"
TITLE="LISTEN"
HREF="sql-listen.html"></HEAD
><BODY
BGCOLOR="#FFFFFF"
TEXT="#000000"
><DIV
CLASS="NAVHEADER"
><TABLE
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TH
COLSPAN="3"
ALIGN="center"
>PostgreSQL User's Guide</TH
></TR
><TR
><TD
WIDTH="10%"
ALIGN="left"
VALIGN="bottom"
><A
HREF="sql-grant.html"
>Prev</A
></TD
><TD
WIDTH="80%"
ALIGN="center"
VALIGN="bottom"
></TD
><TD
WIDTH="10%"
ALIGN="right"
VALIGN="bottom"
><A
HREF="sql-listen.html"
>Next</A
></TD
></TR
></TABLE
><HR
ALIGN="LEFT"
WIDTH="100%"></DIV
><H1
>INSERT</H1
><DIV
CLASS="REFNAMEDIV"
><H2
>Name</H2
>INSERT &#8212; Inserts new rows into a table</DIV
><DIV
CLASS="REFSYNOPSISDIV"
><PRE
CLASS="SYNOPSIS"
>INSERT INTO <TT
CLASS="REPLACEABLE"
><I
>table</I
></TT
> [ ( <TT
CLASS="REPLACEABLE"
><I
>column</I
></TT
> [, ...] ) ]
    { VALUES ( <TT
CLASS="REPLACEABLE"
><I
>expression</I
></TT
> [, ...] ) | SELECT <TT
CLASS="REPLACEABLE"
><I
>query</I
></TT
> }</PRE
><DIV
CLASS="REFSECT2"
><H3
>Inputs</H3
><P
></P
><P
></P
><DL
><DT
><TT
CLASS="REPLACEABLE"
><I
>table</I
></TT
></DT
><DD
><P
>The name of an existing table.&#13;</P
></DD
><DT
><TT
CLASS="REPLACEABLE"
><I
>column</I
></TT
></DT
><DD
><P
>The name of a column in <TT
CLASS="REPLACEABLE"
><I
>table</I
></TT
>.&#13;</P
></DD
><DT
><TT
CLASS="REPLACEABLE"
><I
>expression</I
></TT
></DT
><DD
><P
>A valid expression or value to assign to <TT
CLASS="REPLACEABLE"
><I
>column</I
></TT
>.&#13;</P
></DD
><DT
><TT
CLASS="REPLACEABLE"
><I
>query</I
></TT
></DT
><DD
><P
>A valid query. Refer to the SELECT statement for a further description
          of valid arguments.&#13;</P
></DD
></DL
></DIV
><DIV
CLASS="REFSECT2"
><H3
>Outputs</H3
><P
>&#13;<P
></P
></P><DL
><DT
><SPAN
CLASS="RETURNVALUE"
>INSERT <TT
CLASS="REPLACEABLE"
><I
>oid</I
></TT
></SPAN
> 1</DT
><DD
><P
>Message returned if only one row was inserted.
<SPAN
CLASS="RETURNVALUE"
><TT
CLASS="REPLACEABLE"
><I
>oid</I
></TT
></SPAN
>
 is the row identifier.&#13;</P
></DD
><DT
><SPAN
CLASS="RETURNVALUE"
>INSERT 0 <TT
CLASS="REPLACEABLE"
><I
>#</I
></TT
></SPAN
></DT
><DD
><P
>Message returned if more than one rows were inserted.
<SPAN
CLASS="RETURNVALUE"
><TT
CLASS="REPLACEABLE"
><I
>#</I
></TT
></SPAN
>
 is the number of rows inserted.&#13;</P
></DD
></DL
><P>&#13;</P
></DIV
></DIV
><DIV
CLASS="REFSECT1"
><H2
>Description</H2
><P
>   INSERT allows one to insert new rows into a table. One can insert
   a single row at time or several rows as a result of a query.
   The columns in the target list may be listed in any order.
   In every column not present in the target list will be inserted 
   the default value, if column has not a declared default value
   it will be assumed as NULL. If the expression for each column
   is not of the correct data type, automatic type coercion will be
   attempted.&#13;</P
><P
>   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.&#13;</P
></DIV
><DIV
CLASS="REFSECT1"
><H2
>Usage</H2
><P
><PRE
CLASS="PROGRAMLISTING"
>--Insert a single row into table films;
--(in the second example the column date_prod is omitted 
--therefore will be stored in it a default value of NULL):
--
INSERT INTO films VALUES
    ('UA502','Bananas',105,'1971-07-13','Comedy',INTERVAL '82 minute');

INSERT INTO films (code, title, did, date_prod, kind)
    VALUES ('T_601', 'Yojimbo', 106, DATE '1961-06-16', 'Drama');</PRE
>

<PRE
CLASS="PROGRAMLISTING"
>--Insert a single row into table distributors, note that
--only column "name" is specified, to the non specified
--column "did" will be assigned its default value:
--
INSERT INTO distributors (name) VALUES ('British Lion');</PRE
>

<PRE
CLASS="PROGRAMLISTING"
>--Insert several rows into table films from table tmp:
--
INSERT INTO films
    SELECT * FROM tmp;</PRE
>

<PRE
CLASS="PROGRAMLISTING"
>--Insert into arrays:
--Create an empty 3x3 gameboard for noughts-and-crosses
--(all of these queries create the same board attribute)
--(Refer to the <I
CLASS="CITETITLE"
>PostgreSQL User's Guide</I
> for further
--information about arrays).

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,'{{,,},{,,},{,,}}');</PRE
>&#13;</P
></DIV
><DIV
CLASS="REFSECT1"
><H2
>Compatibility</H2
><P
></P
><DIV
CLASS="REFSECT2"
><H3
>SQL92</H3
><P
>The INSERT statement is fully compatible with <SPAN
CLASS="ACRONYM"
>SQL92</SPAN
>.
Possible limitations in features of the 
<TT
CLASS="REPLACEABLE"
><I
>query</I
></TT
>
clause are documented for the SELECT statement.&#13;</P
></DIV
></DIV
><DIV
CLASS="NAVFOOTER"
><HR
ALIGN="LEFT"
WIDTH="100%"><TABLE
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
><A
HREF="sql-grant.html"
>Prev</A
></TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
HREF="user.html"
>Home</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
><A
HREF="sql-listen.html"
>Next</A
></TD
></TR
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
>GRANT</TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
HREF="sql-commands.html"
>Up</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
>LISTEN</TD
></TR
></TABLE
></DIV
></BODY
></HTML
>