如何在PostgreSQL中创建表

PostgreSQL is popular with enterprises across the world and is used in various industries, including manufacturing.

PostgreSQL在全世界的企业中都很流行，并且在包括制造业在内的各种行业中使用。

In industrial manufacturing, reliability is key. Production site downtimes can cost companies millions of dollars. This makes PostgreSQL an ideal choice in such situations as it can be configured for full redundancy, automatic failover, and nearly no downtime upgrades.

在工业制造中，可靠性是关键。 生产站点停机可能使公司损失数百万美元。 这使得PostgreSQL在这种情况下成为理想选择，因为可以对其进行配置以实现完全冗余，自动故障转移并且几乎不需要停机升级。

什么是PostgreSQL？ (What is PostgreSQL?)

PostgreSQL is an object relational database management system. It is also called “Postgres” and is extendible, enabling one to add custom procedures developed by different programming languages such as Java, C and C++ etc.

PostgreSQL是一个对象关系数据库管理系统。 它也被称为“ Postgres”，并且是可扩展的，从而使它能够添加由不同编程语言(例如Java，C和C ++等)开发的自定义过程。

PostgreSQL主要优点 (Key benefits of PostgreSQL)

Some of the applications and software we use and enjoy everyday utilize PostgreSQL in the backend.

我们每天都会使用并享受的一些应用程序和软件在后端使用PostgreSQL。

Here are a few compelling reasons businesses rely on it so much:

以下是一些企业非常依赖它的令人信服的原因：

1. It is secure, as it is constantly being updated and advanced.
   它是安全的，因为它会不断更新和改进。
2. It is reliable and can be used in environments that need high availability for maintaining business continuity.
   它是可靠的，可用于需要高可用性以维持业务连续性的环境中。
3. High performance, as compared to MySQL and compatible with most major operating systems. It offers a variety of programming languages.
   与MySQL相比，高性能，并且与大多数主要操作系统兼容。 它提供了多种编程语言。
4. It is open source and free, allowing it to be used with zero capital cost for projects.
   它是开放源代码且免费的，允许以零资本成本用于项目。
5. It is easy to download and use.
   易于下载和使用。

It is little wonder then that a recent survey revealed that 63.9% developers worldwide ranked PostgreSQL as their preferred database management system, expressing an interest to continue using it for years to come.

毫不奇怪， 最近的一项调查显示，全世界有63.9％的开发人员将PostgreSQL列为其首选的数据库管理系统，并表示有兴趣在未来几年内继续使用它。

在PostgreSQL中创建表 (Creating Tables in PostgreSQL)

Being a relational database, tables are an important feature of PostgreSQL, which consists of multiple related tables. Today, we will look at how to create and manage such tables with a simple example.

作为关系数据库，表是PostgreSQL重要功能，它由多个相关表组成。 今天，我们将通过一个简单的示例介绍如何创建和管理此类表。

By default, PostgreSQL uses PgAdmin(GUI) to interact with Postgres Database.

默认情况下，PostgreSQL使用PgAdmin(GUI)与Postgres数据库进行交互。

It provides features to perform several common database operations such as copy tables, schema and tables with data from one database server to another database server.

它提供了执行几种常见数据库操作(例如复制)的功能 数据从一个数据库服务器到另一数据库服务器的表，模式和表。

It also supports all generic functions — Create, rename, update, delete etc.

它还支持所有通用功能- 创建 ， 重命名 ， 更新 ， 删除等。

Before we start working with multiple tables, we need to create tables for that.

在开始使用多个表之前，我们需要为此创建表。

For our example, we will create 2 tables:

对于我们的示例，我们将创建2个表：

1. USERS

   用户

2. STUDENT

   学生

Now let us proceed step by step……

现在让我们逐步进行……

1.创建我们的表 (1. Creating our tables)

• Our first table — USERS

  我们的第一张桌子-用户

CREATE TABLE USERS (
id SERIAL NOT NULL PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
email VARCHAR(50) NOT NULL DEFAULT ‘abc@gmail.com’,
address VARCHAR(100),
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW (),
updated at TIMESTAMPTZ NOT NULL DEFAULT NOW ()
);

• Our second table — STUDENT

  我们的第二张桌子—学生

CREATE TABLE STUDENT(
id SERIAL NOT NULL PRIMARY KEY,
iduser INTEGER NOT NULL,
branch VARCHAR(20) NOT NULL DEFAULT ‘CSE’,
batch VARCHAR(10) NOT NULL DEFAULT ‘2020–2021’,
idlibrary VARCHAR(10),
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

We have now created both tables successfully.

现在，我们已经成功创建了两个表。

Now we write a trigger procedure to update the “updated_at” column in both these tables.

现在，我们编写一个触发过程来更新这两个表中的“ updated_at ”列。

2.创建触发器 (2. Creating the Trigger)

• Creating our trigger

  创建触发器

CREATE OR REPLACE FUNCTION update_date()
RETURNS TRIGGER AS $$
BEGIN
NEW.updated_at = NOW()
RETURN NEW
END;
$$ LANGUAGE plpgsql

• Applying the trigger to USERS table(updated_at)

  将触发器应用于USERS表(updated_at)

CREATE TRIGGER update_time
BEFORE UPDATE
ON USERS
FOR EACH ROW
EXECUTE PROCEDURE update_date()

• Applying the trigger to the STUDENT table(updated_at)

  将触发器应用于STUDENT表(updated_at)

CREATE TRIGGER update_time
BEFORE UPDATE
ON STUDENT
FOR EACH ROW
EXECUTE PROCEDURE update_date()

We have successfully created our trigger and applied it to both tables. On to the next step.

我们已经成功创建了触发器并将其应用于两个表。 继续下一步。

3. Inserting data into both tables

3.将数据插入两个表

Now we insert the data into both tables where student data is dependent on the user’s data, using a single query.

现在，我们使用一个查询将数据插入到两个表中，其中学生数据取决于用户数据。

WITH inserted_data AS(
INSERT INTO USERS(first_name, last_name, email, address)
VALUES (‘John’, ‘Mccain’,’john@gmail.com’, ‘Coco solo’)
RETURNING id
)
INSERT INTO STUDENT(iduser,branch,batch,idlibrary) VALUES(
(SELECT id FROM inserted_data), ‘CSE’, ‘2020–2021’, ‘JOHN1234’
);

As shown above, we have now inserted data successfully in both tables and can insert some more data in a similar fashion.

如上所示，我们现在已经成功地在两个表中插入了数据，并且可以以类似的方式插入更多数据。

4.从我们的表中获取数据 (4. Fetching data from our tables)

Use the join clause to fetch data from both the tables.

使用join子句从两个表中获取数据。

SELECT u.id, u.first_name,u.last_name,u.email,u.address,s.*
FROM USERS u
JOIN STUDENT s
ON u.id=s.iduser;

5.使用我们表中的数据 (5. Using the data in our tables)

Update query can be used in both ways separately, or combined as well. Here we go with the latter option.

更新查询可以单独使用，也可以组合使用。 在这里，我们选择后一种选择。

UPDATE STUDENT
SET batch=’2021–2022', branch=’EN’
FROM USERS
WHERE USERS.id=STUDENT.iduser
AND USERS.id=3;

6.在表中添加新列 (6. Adding new columns in the table)

Sometimes we may need to add a new column into an existing table. We can do so with the code below:

有时我们可能需要在现有表中添加新列。 我们可以使用以下代码进行操作：

ALTER TABLE STUDENT
ADD COLUMN semester INTEGER NOT NULL DEFAULT 1;

7.重命名列 (7. Renaming a column)

Now further we can rename the column names as well-

现在，我们还可以重命名列名称-

ALTER TABLE STUDENT
RENAME COLUMN idlibrary TO library_id;

8.删除数据库中存在的所有模式 (8. Drop all schemas present in the database)

In case we want to drop all the tables present in the database, we only need to run the query below:

如果我们要删除数据库中存在的所有表，我们只需要运行以下查询：

DO $$ DECLARE
record RECORD
BEGIN
FOR record IN (SELECT table_name FROM pg_table WHERE schema = current_schema())
LOOP
EXECUTE ‘DROP TABLE IF EXISTS’ || quote_ident(record.table_name) ||’ CASCADE’
END LOOP
END $$

结论 (Conclusion)

The above code will help you create tables in PostgreSQL and is a small part of what is possible. Being open source, extendible, and secure, postgreSQL offers a robust solutions for businesses and developers everywhere.

上面的代码将帮助您在PostgreSQL中创建表，这只是可能的一小部分。 作为开源，可扩展和安全的PostgreSQL，它为各地的企业和开发人员提供了可靠的解决方案。

Author — Ajeet Kumar Maurya, DLT Labs™

作者— ALTet Kumar Maurya， DLT Labs ™

About Ajeet Kumar Maurya: Ajeet is a software engineer at DLT Labs. He is a part of the DL Certify team and is skilled in Node.js, PSQL, JS, MongoDB, Postgres.

关于Ajeet Kumar Maurya： Ajeet是DLT Labs的软件工程师。 他是DL Certify团队的成员，并且精通Node.js，PSQL，JS，MongoDB和Postgres。

https://www.postgresqltutorial.com/

https://www.postgresqltutorial.com/

https://www.udemy.com/

https://www.udemy.com/

https://www.2ndquadrant.com/en/postgresql/business-case-postgresql/

https://www.2ndquadrant.com/zh-CN/postgresql/business-case-postgresql/

https://www.cybertec-postgresql.com/en/postgresql-overview/solutions-who-uses-postgresql/

https://www.cybertec-postgresql.com/zh-CN/postgresql-overview/solutions-who-uses-postgresql/

https://www.postgresqltutorial.com/postgresql-create-table/

https://www.postgresqltutorial.com/postgresql-create-table/

翻译自: https://medium.com/@dltlabs/how-to-create-tables-in-postgressql-d94bc1f2c3ac