Postgresql 简明教程

PostgreSQL - CREATE Table

PostgreSQL CREATE TABLE 语句用于在任何给定数据库中创建一个新表。

The PostgreSQL CREATE TABLE statement is used to create a new table in any of the given database.

Syntax

CREATE TABLE 语句的基本语法如下 -

Basic syntax of CREATE TABLE statement is as follows −

CREATE TABLE table_name(
   column1 datatype,
   column2 datatype,
   column3 datatype,
   .....
   columnN datatype,
   PRIMARY KEY( one or more columns )
);

CREATE TABLE 是一个关键字，它告诉数据库系统创建一个新表。表的唯一名称或标识符位于 CREATE TABLE 语句之后。最初，当前数据库中的空表归属发出该命令的用户。

CREATE TABLE is a keyword, telling the database system to create a new table. The unique name or identifier for the table follows the CREATE TABLE statement. Initially, the empty table in the current database is owned by the user issuing the command.

然后，在括号中，会有一个列表，它定义了表中的每一列以及它们是什么类型的数据。该语法将在下面给出的一个示例中明确显示。

Then, in brackets, comes the list, defining each column in the table and what sort of data type it is. The syntax will become clear with an example given below.

Examples

以下是一个示例，它创建了一个以 ID 作为主键、NOT NULL 作为约束的 COMPANY 表，指示在该表中创建记录时这些字段不能为 NULL -

The following is an example, which creates a COMPANY table with ID as primary key and NOT NULL are the constraints showing that these fields cannot be NULL while creating records in this table −

CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

让我们再创建一个将在后续章节中用于练习的表 -

Let us create one more table, which we will use in our exercises in subsequent chapters −

CREATE TABLE DEPARTMENT(
   ID INT PRIMARY KEY      NOT NULL,
   DEPT           CHAR(50) NOT NULL,
   EMP_ID         INT      NOT NULL
);

你可以使用 \d 命令来验证你的表是否已成功创建，该命令将用来列出已附加数据库中的所有表。

You can verify if your table has been created successfully using \d command, which will be used to list down all the tables in an attached database.

testdb-# \d

以上给出的 PostgreSQL 语句将产生以下结果 -

The above given PostgreSQL statement will produce the following result −

           List of relations
 Schema |    Name    | Type  |  Owner
--------+------------+-------+----------
 public | company    | table | postgres
 public | department | table | postgres
(2 rows)

使用 \d tablename 来描述每个表，如下所示 -

Use \d tablename to describe each table as shown below −

testdb-# \d company

以上给出的 PostgreSQL 语句将产生以下结果 -

The above given PostgreSQL statement will produce the following result −

        Table "public.company"
  Column   |     Type      | Modifiers
-----------+---------------+-----------
 id        | integer       | not null
 name      | text          | not null
 age       | integer       | not null
 address   | character(50) |
 salary    | real          |
 join_date | date          |
Indexes:
    "company_pkey" PRIMARY KEY, btree (id)