如何在云服务器上的PostgreSQL中创建，删除和管理表

什么是PostgreSQL？

PostgreSQL是一个使用SQL查询语言的数据库管理系统。 它是一个非常稳定和功能丰富的数据库系统，可用于存储来自您的VPS上其他应用程序的数据。

在本文中，我们将讨论如何在PostgreSQL界面中创建和管理表。 您将学习如何正确配置表，并使用它们来存储您的信息。

如何在Ubuntu上安装和登录PostgreSQL

在本指南中，我们将在Ubuntu 12.04上安装PostgreSQL，但它应该在大多数其他发行版的默认存储库中可用。

键入以下命令以安装：

sudo apt-get update
sudo apt-get install postgresql postgresql-contrib

安装后，创建一个新用户来管理我们将要创建的数据库：

sudo adduser postgres_user

登录到默认的PostgreSQL用户（称为“postgres”）以创建数据库并将其分配给新用户：

sudo su - postgres
psql

您将被放入PostgreSQL命令提示符。

创建与您创建的系统用户匹配的新用户。 然后创建一个由该用户管理的数据库：

CREATE USER postgres_user WITH PASSWORD 'password';
CREATE DATABASE my_postgres_db OWNER postgres_user;

使用以下命令退出界面：

\q

退出默认的“postgres”用户帐户，并使用以下命令登录到您创建的用户：

exit
sudo su - postgres_user

使用以下命令登录到您创建的数据库：

psql my_postgres_db

我们现在准备好了解表管理。

PostgreSQL中的表创建语法

我们的数据库还没有任何表格。 我们可以通过要求PostgreSQL使用这个命令给我们列出可用的表来验证：

\d

No relations found.

我们可以通过遵循以下语法创建一个新表：

CREATE TABLE new_table_name (
	table_column_title TYPE_OF_DATA column_constraints,
	next_column_title TYPE_OF_DATA column_constraints,
	table_constraint
	table_constraint
) INHERITS existing_table_to_inherit_from;

在右括号之后直到分号的部分是一个可选结构，用于继承先前定义中列出的列之外的现有表中的所有列。

括号内的部分分为两部分：列定义和表约束。

PostgreSQL列和表定义

列定义遵循此语法模式：

column_name data_type (optional_data_length_restriction) column_constraints

列名称应该是自解释的。

PostgreSQL数据类型

数据类型可以是以下任何一种：

• 布尔 ：使用“布尔”或“布尔”申报true或false值。
• 字符值
  • 焦炭 ：包含一个字符
  • CHAR（＃）：持有的字符＃编号。 将插入空格以填充任何额外的空间。
  • VARCHAR（＃）：最多字符数＃持有。 可以少含。
• 整数值
  • SMALLINT：-32768到32767之间的整数。
  • INT：-214783648与214783647之间的整数。
  • 串行 ：自动填充整数。
• 浮点值
  • 浮动（＃）：浮点数有精度至少为＃点。
  • 实 ：8个字节的浮点数
  • 数字（＃，after_dec）：用数字＃号实数，小数点后的数字after_dec
• 日期和时间值
  • 日期 ：存储日期值
  • 时间 ：存储时间值
  • 时间戳 ：存储日期和时间值
  • timestamptz：存储时间戳，其中包括时区数据
  • 间隔 ：存储两个时间戳值之间的差
• 几何数据
  • 点 ：存储在一对限定一个点的坐标的
  • 行 ：存储一组点，映射出了一条
  • LSEG：一个定义一个线段存储数据
  • 框 ：定义一个矩形存储数据
  • 多边形 ：定义的任何封闭的空间存储数据
• 设备规格
  • INET：存储IP地址
  • MACADDR：存储设备的MAC地址

PostreSQL列和表约束

列定义还可以具有为列中找到的数据类型提供规则的约束。 以下可以用作数据类型后面的空格分隔值：

• NOT NULL：列不能为空值
• UNIQUE：列的值不能成为任何记录是相同的。 Null始终被认为是唯一的值
• PRIMARY KEY：上述两个约束的组合。 每个表只能使用一次
• 检查 ：确保条件为真为值在列
• 参考文献 ：值必须存在于一个列中的另一个表

在定义列之后，可以声明表宽约束。 表宽约束可以是UNIQUE，PRIMARY KEY，CHECK或REFERENCES。

如何在PostgreSQL中创建表

让我们创建一个测试表来练习。 我们将创建一个名为“pg_equipment”的表，定义各种游乐设备。 键入以下表定义：

CREATE TABLE pg_equipment (
	equip_id serial PRIMARY KEY,
	type varchar (50) NOT NULL,
	color varchar (25) NOT NULL,
	location varchar(25) check (location in ('north', 'south', 'west', 'east', 'northeast', 'southeast', 'southwest', 'northwest')),
	install_date date
	);

NOTICE:  CREATE TABLE will create implicit sequence "pg_equipment_equip_id_seq" for serial column "pg_equipment.equip_id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "pg_equipment_pkey" for table "pg_equipment"
CREATE TABLE

我们可以看到我们的新表，在提示符中输入“\ d”：

\d

                       List of relations
 Schema |           Name            |   Type   |     Owner     
--------+---------------------------+----------+---------------
 public | pg_equipment              | table    | postgres_user
 public | pg_equipment_equip_id_seq | sequence | postgres_user
(2 rows)

列出该表以及由“equip_id”串行数据类型声明创建的序列。

如何在PostgreSQL中改变表数据

我们可以使用以下一般语法更改表的定义：

ALTER TABLE table_name Action_TO_Take;

例如，我们可以通过输入以下命令为我们的“pg_equipment”表添加一列：

ALTER TABLE pg_equipment ADD COLUMN functioning bool;

ALTER TABLE

我们可以通过键入以下内容查看额外的列：

\d pg_equipment

    Column    |         Type          |                            Modifiers                            
--------------+-----------------------+-----------------------------------------------------------------
 equip_id     | integer               | not null default nextval('pg_equipment_equip_id_seq'::regclass)
 type         | character varying(50) | not null
 color        | character varying(25) | not null
 location     | character varying(25) | 
 install_date | date                  | 
 functioning  | boolean               |
 . . .

要添加指定“除非另有说明，设备应被视为工作”的默认值，请提供以下命令：

ALTER TABLE pg_equipment ALTER COLUMN functioning SET DEFAULT 'true';

如果我们想要确保该值也不为null，我们可以这样做：

ALTER TABLE pg_equipment ALTER COLUMN functioning SET NOT NULL;

要重命名列，请使用以下语法：

ALTER TABLE pg_equipment RENAME COLUMN functioning TO working_order;

要删除刚创建的列，请输入以下命令：

ALTER TABLE pg_equipment DROP COLUMN working_order;

我们可以用这个命令重命名整个表：

ALTER TABLE pg_equipment RENAME TO playground_equip;

删除PostgreSQL中的表

我们可以通过键入以下内容删除我们创建的表：

DROP TABLE playground_equip;

DROP TABLE

如果我们将该命令提供给不存在的表，我们将收到以下错误：

ERROR: table "playground_equip" does not exist

为了避免这个错误，我们可以告诉postgreSQL删除表，如果它存在并成功返回成功。 我们通过发出以下命令来做到这一点：

DROP TABLE IF EXISTS playground_equip;

NOTICE:  table "playground_equip" does not exist, skipping
DROP TABLE

这一次，它告诉我们，没有找到表，但是继续，而不是抛出一个错误。

结论

您现在应该知道在PostgreSQL中创建和管理简单表。 如果您正在从另一个应用程序管理数据，或者从命令行学习如何控制PostgreSQL，这些技能将非常有用。