提要:在本教程中,我们将向您展示如何使用PostgreSQL DROP COLUMN语句从现有的数据库表中删除一个或多个字段。

语法

要从一张表删除字段,需要使用 ALTER TABLEDROP COLUMN 子句,如下所示:

ALTER TABLE table_name 
DROP COLUMN column_name;

当删除一个字段时,PostgreSQL 将自动删除与该字段相关的约束和索引。

如果想在删除该字段时,同时删除依赖该字段的其它对象,可以添加 CASCADE,如下所示:

ALTER TABLE table_name 
DROP COLUMN column_name CASCADE;

如果尝试删除一个不存在的字段,PostgreSQL 将抛出一个错误。要解决这个问题,只需要添加 IF EXISTS

ALTER TABLE table_name 
DROP COLUMN IF EXISTS column_name;

要删除多个字段,只需要添加多条 DROP COLUMN 语句:

ALTER TABLE table_name
DROP COLUMN column_name_1,
DROP COLUMN column_name_2,
...;

示例

我们创建三张表来作演示:

CREATE TABLE publishers (
    publisher_id serial PRIMARY KEY,
    name VARCHAR NOT NULL
);

CREATE TABLE categories (
    category_id serial PRIMARY KEY,
    name VARCHAR NOT NULL
);

CREATE TABLE books (
    book_id serial PRIMARY KEY,
    title VARCHAR NOT NULL,
    isbn VARCHAR NOT NULL,
    published_date DATE NOT NULL,
    description VARCHAR,
    category_id INT NOT NULL,
    publisher_id INT NOT NULL,
    FOREIGN KEY (publisher_id) REFERENCES publishers (publisher_id),
    FOREIGN KEY (category_id) REFERENCES categories (category_id)
);

再创建一个视图:

CREATE VIEW book_info AS SELECT
    book_id,
    title,
    isbn,
    published_date,
    name
FROM
    books b
INNER JOIN publishers P ON P .publisher_id = b.publisher_id
ORDER BY
    title;

现在,要删除 books 表里的 category_id

ALTER TABLE books DROP COLUMN category_id;

尝试删除该表中的 publisher_id 字段:

ALTER TABLE books DROP COLUMN publisher_id;

将触发以下错误:

ERROR:  cannot drop table books column publisher_id because other objects depend on it
DETAIL:  view book_info depends on table books column publisher_id
HINT:  Use DROP ... CASCADE to drop the dependent objects too.

这是因为 book_info 视图引用了 books 表的 publisher_id,要解决这个错误,可以使用下面的语句:

ALTER TABLE books DROP COLUMN publisher_id CASCADE;

要同时删除 isbndescription 字段,可以使用下面的语句:

ALTER TABLE books 
  DROP COLUMN isbn,
  DROP COLUMN description;