INSERT - Inserts new rows into a table


SYNOPSIS

       INSERT INTO table [ ( column [, ...] ) ]
           { DEFAULT VALUES | VALUES ( expression [, ...] ) | SELECT query }

   INPUTS
       table  The name of an existing table.

       column The name of a column in table.

       DEFAULT VALUES
              All  columns  will  be filled by NULLs or by values
              specified when the table was created using  DEFAULT
              clauses.

       expression
              A valid expression or value to assign to column.

       query  A  valid query. Refer to the SELECT statement for a
              further description of valid arguments.

   OUTPUTS
       INSERT oid 1
              Message returned if only one row was inserted.  oid
              is the numeric OID of the inserted row.

       INSERT 0 #
              Message   returned  if  more  than  one  rows  were
              inserted.  # is the number of rows inserted.


DESCRIPTION

       INSERT 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.

       Each  column  not  present  in  the  target  list  will be
       inserted using a default value, either a declared  DEFAULT
       value  or  NULL.  Postgres will reject the new column if a
       NULL is inserted into a column declared NOT NULL.

       If the expression for each column is not  of  the  correct
       data type, automatic type coercion will be attempted.

       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.

       Insert a single row into table films:

       INSERT INTO films VALUES
           ('UA502','Bananas',105,'1971-07-13','Comedy',INTERVAL '82 minute');

       In  this second example the last column len is omitted and
       therefore it will have the default value of NULL:

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

       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:

       INSERT INTO distributors (name) VALUES ('British Lion');

       Insert several rows into table films from table tmp:

       INSERT INTO films SELECT * FROM tmp;

       Insert into arrays (refer to the PostgreSQL  User's  Guide
       for further information about arrays):

       -- 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,'{{,,},{,,},{,,}}');


COMPATIBILITY

   SQL92
       INSERT  is  fully compatible with SQL92.  Possible limita­
       tions in features of the query clause are  documented  for
       SELECT [select(l)].


Man(1) output converted with man2html