This page in other versions: 9.2 / 9.3 / 9.4 / 9.5 / current (9.6)  |  Development versions: devel  |  Unsupported versions: 7.1 / 7.2 / 7.3 / 7.4 / 8.0 / 8.1 / 8.2 / 8.3 / 8.4 / 9.0 / 9.1

INSERT

Name

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.

Usage

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 limitations in features of the query clause are documented for SELECT.

Submit correction

If you see anything in the documentation that is not correct, does not match your experience with the particular feature or requires further clarification, please use this form to report a documentation issue.

Privacy Policy | About PostgreSQL
Copyright © 1996-2016 The PostgreSQL Global Development Group