Postgresql 中文操作指南

2.3. Creating a New Table #

您可以通过指定表名称以及所有列名称及其类型来创建新表：

You can create a new table by specifying the table name, along with all column names and their types:

CREATE TABLE weather (
    city            varchar(80),
    temp_lo         int,           -- low temperature
    temp_hi         int,           -- high temperature
    prcp            real,          -- precipitation
    date            date
);

您可以通过行中断将此内容输入到 psql。psql 将识别到在分号处之前，该命令均未终止。

You can enter this into psql with the line breaks. psql will recognize that the command is not terminated until the semicolon.

可在 SQL 命令中自由使用空格（即空格、制表符和换行符）。这意味着您可以按照与上述不同的方式键入此命令，甚至可以全部写入一行。两个破折号 (“—”) 表示注释。无论什么内容紧随它们，都将被忽略直至行尾。SQL 对关键字和标识符大小写不敏感，除非双引号引住了标识符以便保留大小写（上面没有采用这种方式）。

White space (i.e., spaces, tabs, and newlines) can be used freely in SQL commands. That means you can type the command aligned differently than above, or even all on one line. Two dashes (“—”) introduce comments. Whatever follows them is ignored up to the end of the line. SQL is case-insensitive about key words and identifiers, except when identifiers are double-quoted to preserve the case (not done above).

varchar(80) 指定一个数据类型，它最多可存储 80 个字符长度的任意字符字符串。int 是常规整数类型。real 是用于存储单精度浮点数的数据类型。date 应该不言自明。（是的，类型为 date 的列也名为 date。这可能很方便，也可能令人困惑——由您选择。）

varchar(80) specifies a data type that can store arbitrary character strings up to 80 characters in length. int is the normal integer type. real is a type for storing single precision floating-point numbers. date should be self-explanatory. (Yes, the column of type date is also named date. This might be convenient or confusing — you choose.)

PostgreSQL 支持标准 SQL 类型 int、smallint、real、double precision、char(_N), _varchar(_N), _date、time、timestamp 和 interval，以及其他普通用途类型和丰富的几何类型。可以使用任意数量的用户自定义数据类型对 PostgreSQL 进行自定义。因此，除 SQL 标准中要求支持特殊情况的地方，否则类型名称不是语法中的关键字。

PostgreSQL supports the standard SQL types int, smallint, real, double precision, char(_N), _varchar(_N), _date, time, timestamp, and interval, as well as other types of general utility and a rich set of geometric types. PostgreSQL can be customized with an arbitrary number of user-defined data types. Consequently, type names are not key words in the syntax, except where required to support special cases in the SQL standard.

第二个示例将存储城市及其相关地理位置：

The second example will store cities and their associated geographical location:

CREATE TABLE cities (
    name            varchar(80),
    location        point
);

point 类型是 PostgreSQL 特定数据类型的一个示例。

The point type is an example of a PostgreSQL-specific data type.

最后，值得注意的是，如果您不再需要表或想以不同的方式重新创建它，可以使用以下命令将其删除：

Finally, it should be mentioned that if you don’t need a table any longer or want to recreate it differently you can remove it using the following command:

DROP TABLE tablename;