NAME

INSERT - 在表中創建新行

SYNOPSIS

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

DESCRIPTION 描述

INSERT 允許我們向表中插入新行。 我們可以一次插入一行或多行作為查詢結果。

 目標列表中的列/字段可以按任何順序排列。 在目標列中沒有出現的列/字段將插入缺省值， 可能是定義了的缺省值或者 NULL。

 如果每行的表達式不是正確的數據類型，系統將試圖進行自動的類型轉換。

 要想向表中插入數據，你必須有 INSERT 權限， 如果你使用了 query 子句插入來自查詢里的數據行， 你還需要擁有在查詢里使用的表的 SELECT 權限。  

PARAMETERS 參數

table

 現存表的名稱（可以有模式修飾）。

column

 表 table 中的字段名。

DEFAULT VALUES

 所有字段都會用它們的缺省值填充。

expression

 賦予 column 的一個有效表達式或值。

DEFAULT

 這個字段將被字段它的填充。

query

 一個查詢（SELECT 語句），它提供插入的數據行。 請參考 SELECT 語句獲取語法描述。

OUTPUTS 輸出

 成功完成后，一條 INSERT 命令返回一個下面形式的命令標簽

INSERT oid count

count 是插入的行數。 如果 count 正好是一，并且目標表有 OID， 那么 oid 是賦予插入行的 OID。 否則 oid 是零。  

EXAMPLES 例子

 向表 films 里插入一行：

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

 在第二個例子里面省略了字段 len  因此在它里面將只存儲缺省的 NULL 值：

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

 在第三個例子里，我們用 DEFAULT 值作為數據字段，而不是聲明一個數值：

INSERT INTO films VALUES
    ('UA502', 'Bananas', 105, DEFAULT, 'Comedy', '82 minutes');
INSERT INTO films (code, title, did, date_prod, kind)
    VALUES ('T_601', 'Yojimbo', 106, DEFAULT, 'Drama');

 從表 tmp 中插入幾行到表 films 中：

INSERT INTO films SELECT * FROM tmp;

 插入數組：

-- 創建一個空的 3x3 游戲板來玩圈-和-叉游戲
-- (所有這些查詢創建相同的游戲)
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 兼容性

INSERT 完全遵循 SQL 標準。可能碰到的關于 query 子句特性的限制在 SELECT [select(7)] 語句中有相關文檔。  

#p#

NAME

INSERT - create new rows in a table

SYNOPSIS

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

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 its declared default value or null.

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

You must have INSERT privilege to a table in order to insert into it. If you use the query clause to insert rows from a query, you also need to have SELECT privilege on any table used in the query.  

PARAMETERS

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 with their default values.

expression

An expression or value to assign to column.

DEFAULT

This column will be filled with its default value.

query

A query (SELECT statement) that supplies the rows to be inserted. Refer to the SELECT statement for a description of the syntax.

OUTPUTS

On successful completion, an INSERT command returns a command tag of the form

INSERT oid count

The count is the number of rows inserted. If count is exactly one, and the target table has OIDs, then oid is the OID assigned to the inserted row. Otherwise oid is zero.  

EXAMPLES

Insert a single row into table films:

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

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, '1961-06-16', 'Drama');

The third example uses the DEFAULT clause for the date columns rather than specifying a value:

INSERT INTO films VALUES
    ('UA502', 'Bananas', 105, DEFAULT, 'Comedy', '82 minutes');
INSERT INTO films (code, title, did, date_prod, kind)
    VALUES ('T_601', 'Yojimbo', 106, DEFAULT, 'Drama');

This examples inserts several rows into table films from table tmp:

INSERT INTO films SELECT * FROM tmp;

This example inserts into array columns:

-- Create an empty 3x3 gameboard for noughts-and-crosses
-- (all of these commands create the same board)
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

INSERT conforms fully to the SQL standard. Possible limitations of the query clause are documented under SELECT [select(7)].