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. PostgreSQL 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');
In the third example, we use the DEFAULT values for the date columns
rather than specifying an entry.
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');
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,'{{,,},{,,},{,,}}');
