修改表
修改表
在 PostgreSQL 中,ALTER TABLE
语句用来修改一个现有的表,包括:重命名表、添加列、删除列、修改列、添加约束、删除约束等
ALTER TABLE 用法概述
由于 ALTER TABLE
语句能修改一个表的方方面面,因此它的它的语法非常复杂
下面说明了该 ALTER TABLE
语句的基本语法:
ALTER TABLE [IF EXISTS] table_name
[alter_action options]
[, ...];
table_name 是要修改的表的名字。IF EXISTS
是可选的,其中 alter_action 是一个修改动作,主要包括以下关键字:
ADD
关键字可用来添加列和约束DROP
关键字可用来删除列和约束ALTER
关键字可用来修改现有的列和约束RENAME
关键字可用来重命名表、列、和约束SET
关键字可用来修改表的架构、表空间ENABLE
关键字可用来启用触发器、规则、和行安全策略DISABLE
关键字可用来禁用触发器、规则、和行安全策略
ALTER TABLE 示例
重命名表
我们将在 testdb 数据库中创建 users 和 user_hobbies 两个表。其中,users 表用来存储用户的名称,性别,年龄等信息。user_hobbies 表用来存储用户的业余爱好
使用以下语句创建 users 表:
CREATE TABLE users (
user_id INTEGER NOT NULL PRIMARY KEY,
name VARCHAR(45) NOT NULL,
age INTEGER,
locked BOOLEAN NOT NULL DEFAULT false,
created_at TIMESTAMP NOT NULL
);
使用以下语句创建 user_hobbies 表:
CREATE TABLE user_hobbies (
hobby_id SERIAL NOT NULL,
user_id INTEGER NOT NULL,
hobby VARCHAR(45) NOT NULL,
created_at TIMESTAMP NOT NULL,
PRIMARY KEY (hobby_id),
CONSTRAINT fk_user
FOREIGN KEY (user_id)
REFERENCES users (user_id)
ON DELETE CASCADE
ON UPDATE RESTRICT);
要查看 user_hobbies 表的定义,请使用 \d
命令:
\d user_hobbies;
Table "public.user_hobbies"
Column | Type | Collation | Nullable | Default
------------+-----------------------------+-----------+----------+------------------------------------------------
hobby_id | integer | | not null | nextval('user_hobbies_hobby_id_seq'::regclass)
user_id | integer | | not null |
hobby | character varying(45) | | not null |
created_at | timestamp without time zone | | not null |
Indexes:
"user_hobbies_pkey" PRIMARY KEY, btree (hobby_id)
Foreign-key constraints:
"fk_user" FOREIGN KEY (user_id) REFERENCES users(user_id) ON UPDATE RESTRICT ON DELETE CASCADE
这里,user_hobbies 表具有一个引用 users 表的外键
假设,您所在的团队制定了新的命名规则,所有的表都需要一个 t_ 开头,因此您需要将 users 表重命名为 t_users,将 user_hobbies 表重命名为 t_user_hobbies
要将 users 表重命名为 t_users,请使用下面的语句:
ALTER TABLE users RENAME TO t_users;
再次使用 \d
命令查看 user_hobbies 表的定义:
\d user_hobbies;
Table "public.user_hobbies"
Column | Type | Collation | Nullable | Default
------------+-----------------------------+-----------+----------+------------------------------------------------
hobby_id | integer | | not null | nextval('user_hobbies_hobby_id_seq'::regclass)
user_id | integer | | not null |
hobby | character varying(45) | | not null |
created_at | timestamp without time zone | | not null |
Indexes:
"user_hobbies_pkey" PRIMARY KEY, btree (hobby_id)
Foreign-key constraints:
"fk_user" FOREIGN KEY (user_id) REFERENCES t_users(user_id) ON UPDATE RESTRICT ON DELETE CASCADE
从输出中可以清楚地看到,外键约束已更新并改为引用 t_users 表
修改表架构
要修改一个表的架构,请使用以下语法:
ALTER TABLE table_name
SET SCHEMA new_schema
修改表空间
要修改一个表的表空间,请使用以下语法:
ALTER TABLE table_name
SET TABLESPACE new_tablespace
添加列
要向一个表中 添加一个列,请使用以下语法:
ALTER TABLE table_name
ADD [COLUMN] [IF NOT EXISTS] column_name data_type [ column_constraint [ ... ] ]
删除列
要从表中 删除一个列,请使用以下语法:
ALTER TABLE table_name
DROP [ COLUMN ] [ IF EXISTS ] column_name [ RESTRICT | CASCADE ]
重命名列
要重命名一个列,请使用以下语法:
ALTER TABLE table_name
RENAME [ COLUMN ] column_name TO new_column_name
修改列类型
要修改一个列的数据类型,请使用以下语法:
ALTER TABLE table_name
ALTER [ COLUMN ] column_name [ SET DATA ] TYPE data_type
为列设置默认值
要修改一个列的默认值,请使用以下语法:
ALTER TABLE table_name
ALTER [ COLUMN ] column_name SET DEFAULT expression
删除列默认值
要删除一个列的默认值,请使用以下语法:
ALTER TABLE table_name
ALTER [ COLUMN ] column_name DROP DEFAULT
为列添加 NOT NULL
要为一个列添加 NOT NULL 约束,请使用以下语法:
ALTER TABLE table_name
ALTER [ COLUMN ] column_name SET NOT NULL
删除列 NOT NULL
要从一个列删除 NOT NULL 约束,请使用以下语法:
ALTER TABLE table_name
ALTER [ COLUMN ] column_name DROP NOT NULL
标识列
要修改一个列为 标识列,请使用以下语法:
ALTER TABLE table_name
ALTER [ COLUMN ] column_name ADD GENERATED { ALWAYS | BY DEFAULT }
AS IDENTITY [ ( sequence_options ) ]
要将一个标识列修改为普通列,请使用以下语法:
ALTER TABLE table_name
ALTER [ COLUMN ] column_name DROP IDENTITY [ IF EXISTS ]
添加约束
要向一个表中添加一个约束,请使用以下语法:
ALTER TABLE table_name
ADD [ CONSTRAINT constraint_name ]
{ CHECK ( expression ) [ NO INHERIT ] |
UNIQUE ( column_name [, ... ] ) index_parameters |
PRIMARY KEY ( column_name [, ... ] ) index_parameters |
EXCLUDE [ USING index_method ] ( exclude_element WITH operator [, ... ] ) index_parameters [ WHERE ( predicate ) ] |
FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) ]
[ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE referential_action ] [ ON UPDATE referential_action ] }
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
删除约束
要从表中删除一个约束,请使用以下语法:
ALTER TABLE table_name
DROP CONSTRAINT [ IF EXISTS ] constraint_name [ RESTRICT | CASCADE ]
重命名约束
要重命名一个约束,请使用以下语法:
ALTER TABLE table_name
RENAME CONSTRAINT constraint_name TO new_constraint_name