Postgresql 中文操作指南

6.1. Inserting Data #

在创建表时，表中不包含任何数据。在数据库变得实际有用之前要做的第一件事是插入数据。每次插入一行数据。你也可以在单个命令中插入多行，但不能插入不完整的行。即便你只知道某些列的值，也必须创建完整的一行。

When a table is created, it contains no data. The first thing to do before a database can be of much use is to insert data. Data is inserted one row at a time. You can also insert more than one row in a single command, but it is not possible to insert something that is not a complete row. Even if you know only some column values, a complete row must be created.

若要创建新行，请使用 INSERT 命令。该命令需要表名和列值。例如，考虑 Chapter 5 中的 products 表：

To create a new row, use the INSERT command. The command requires the table name and column values. For example, consider the products table from Chapter 5:

CREATE TABLE products (
    product_no integer,
    name text,
    price numeric
);

插入一行的命令示例如下：

An example command to insert a row would be:

INSERT INTO products VALUES (1, 'Cheese', 9.99);

数据值以列在表中出现的顺序列出，用逗号分隔。通常，数据值将为常量（常数），但标量表达式也允许使用。

The data values are listed in the order in which the columns appear in the table, separated by commas. Usually, the data values will be literals (constants), but scalar expressions are also allowed.

上述语法存在以下缺点：您需要知道表中列的顺序。为避免这种情况，您还可以显式地列出列。例如，以下两个命令都与上面命令具有相同的效果：

The above syntax has the drawback that you need to know the order of the columns in the table. To avoid this you can also list the columns explicitly. For example, both of the following commands have the same effect as the one above:

INSERT INTO products (product_no, name, price) VALUES (1, 'Cheese', 9.99);
INSERT INTO products (name, price, product_no) VALUES ('Cheese', 9.99, 1);

许多用户认为始终列出列名称是一种好习惯。

Many users consider it good practice to always list the column names.

如果没有所有列的值，您可以省略其中一些。在这种情况下，列将填充为默认值。例如：

If you don’t have values for all the columns, you can omit some of them. In that case, the columns will be filled with their default values. For example:

INSERT INTO products (product_no, name) VALUES (1, 'Cheese');
INSERT INTO products VALUES (1, 'Cheese');

第二种形式为 PostgreSQL 扩展。它从左侧填充列，填充尽可能多的给定值，其余将为默认值。

The second form is a PostgreSQL extension. It fills the columns from the left with as many values as are given, and the rest will be defaulted.

为清楚起见，您还可以显式请求单个列或整个行的默认值：

For clarity, you can also request default values explicitly, for individual columns or for the entire row:

INSERT INTO products (product_no, name, price) VALUES (1, 'Cheese', DEFAULT);
INSERT INTO products DEFAULT VALUES;

您可以在单个命令中插入多行：

You can insert multiple rows in a single command:

INSERT INTO products (product_no, name, price) VALUES
    (1, 'Cheese', 9.99),
    (2, 'Bread', 1.99),
    (3, 'Milk', 2.99);

还可以插入查询结果（可能是无行、一行或多行）：

It is also possible to insert the result of a query (which might be no rows, one row, or many rows):

INSERT INTO products (product_no, name, price)
  SELECT product_no, name, price FROM new_products
    WHERE release_date = 'today';

这提供了用于计算要插入行的 SQL 查询机制 ( Chapter 7) 的全部功能。

This provides the full power of the SQL query mechanism (Chapter 7) for computing the rows to be inserted.

Tip

在一次插入大量数据时，请考虑使用 COPY 命令。它不如 INSERT 命令灵活，但效率更高。请参阅 Section 14.4 以了解有关提高批量加载性能的更多信息。

When inserting a lot of data at the same time, consider using the COPY command. It is not as flexible as the INSERT command, but is more efficient. Refer to Section 14.4 for more information on improving bulk loading performance.