INSERT(l) SQL - Language Statements (2002-11-22) INSERT(l)
NAME
INSERT - create new rows in a table
SYNOPSIS
INSERT INTO table [ ( column [, ...] ) ]
{ DEFAULT VALUES | VALUES ( { expression | DEFAULT } [, ...] ) | SELECT query }
INPUTS
table
The name (optionally schema-qualified) of an existing
table.
column
The name of a column in table.
DEFAULT VALUES
All columns will be filled by null values or by values
specified when the table was created using DEFAULT
clauses.
expression
A valid expression or value to assign to column.
DEFAULT
This column will be filled in by the column DEFAULT
clause, or NULL if a default is not available.
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. PostgreSQL will reject the new column if a NULL is
inserted into a column declared NOT NULL.
Page 1 (printed 3/24/03)
INSERT(l) SQL - Language Statements (2002-11-22) INSERT(l)
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)
Page 2 (printed 3/24/03)
INSERT(l) SQL - Language Statements (2002-11-22) INSERT(l)
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
[select(l)].
Page 3 (printed 3/24/03)
