Populating a Table With Rows

2.4. Populating a Table With Rows

The INSERT statement is used to populate a table with rows:

INSERT INTO weather VALUES ('San Francisco', 46, 50, 0.25,'1994-11-27');

Note that all data types use rather obvious input formats. Constants that are not simple numeric values usually must be surrounded by single quotes ('), as in the example. The date type is actually quite flexible in what it accepts, but for this tutorial we will stick to the unambiguous format shown here.

 

The point type requires a coordinate pair as input, as shown here:

INSERT INTO cities VALUES ('San Francisco', '(-194.0, 53.0)');

The syntax used so far requires you to remember the order of the columns. An alternative syntax allows you to list the columns explicitly:

INSERT INTO weather (city, temp_lo, temp_hi, prcp, date)

VALUES ('San Francisco', 43, 57, 0.0, '1994-11-29');

You can list the columns in a different order if you wish or even omit some columns, e.g., if the precipitation is unknown:

INSERT INTO weather (date, city, temp_hi, temp_lo)

VALUES ('1994-11-29', 'Hayward', 54, 37);

Many developers consider explicitly listing the columns better style than relying on the order implicitly.

 

Please enter all the commands shown above so you have some data to work with in the following sections.

 

You could also have used COPY to load large amounts of data from flat-text files. This is usually faster because the COPY command is optimized for this application while allowing less flexibility than INSERT. An example would be:

COPY weather FROM '/home/user/weather.txt';

where the file name for the source file must be available on the machine running the backend process, not the client, since the backend process reads the file directly. You can read more about the COPY command in  COPY.

 

 

2.4 将行填充到表中

insert语句用以将行插入到表中：

INSERT INTO weather VALUES ('San Francisco', 46, 50, 0.25,'1994-11-27');

注意，每种数据格式可能具有不同的输入方式。像示例中那样，如果不是简单的数值类型，那么输入的值往往需要用单引号引起来。日期数据类型的数据输入是很灵活的，但在本文档中，我们将使用示例中这种显式的明确的格式。

 

point数据类型在输入时需要括号括起来，例如：

INSERT INTO cities VALUES ('San Francisco', '(-194.0, 53.0)');

上面的示例，需要在插入数据时记得列的顺序。接下来的示例，展示显式的指定列顺序以插入数据：

 

INSERT INTO weather (city, temp_lo, temp_hi, prcp, date)

VALUES ('San Francisco', 43, 57, 0.0, '1994-11-29');

 

还可以按不同的列顺序插入数据，或者省略某些列，例如，当prcp列值未知时：

INSERT INTO weather (date, city, temp_hi, temp_lo)

VALUES ('1994-11-29', 'Hayward', 54, 37);

许多开发者认为显式的列出列名称比依赖于隐式的列顺序更加靠谱一些。

 

请确保执行了以上示例中的所有语句，以创建在接下来章节中所需要用到的数据。

 

也可以使用copy命令从文本文件中导入大量数据到数据库中。虽然copy命令不如insert命令灵活，但因为对copy进行了优化，所以copy会更加快一些。示例：

COPY weather FROM '/home/user/weather.txt';

在使用COPY的时候，因为是后台进程直接读取文件，所以示例中的文件必须可以由后台进程直接读取（所以文件不可以在客户端，而应该在服务器端）。参考COPY查看COPY命令的更多信息。