在PostgreSQL中向现有表添加一个默认值列。

Question

在PostgreSQL中向现有表添加一个默认值列。

5

问题:
有没有一种postgres查询可以向现有表中添加新列，并自动为该表的所有行填充某个值，例如"A1"，只需在创建列时执行一次，以便我仍然可以将该列的默认值设置为另一个值，例如"B2"？

为了明确起见，我想要的是这样的：

给定my_table：

name   |   work
------------------------
bob    |  fireman
carl   |  teacher
alice  |  policeman

我的查询

ALTER TABLE my_table 
ADD COLUMN description varchar(100) 
DEFAULT "B2"
COMMAND_I_D_WISH_TO_KNOW "A1";

将 my_table 更改为

name   |   work       | description
-------------------------------------
bob    |  fireman     | "A1"
carl   |  teacher     | "A1"
alice  |  policeman   | "A1"

这样，如果我之后运行查询

INSERT INTO my_table(name, work)
VALUES karen, developer;

my_tables变成

name   |   work       | description
-------------------------------------
bob    |  fireman     | "A1"
carl   |  teacher     | "A1"
alice  |  policeman   | "A1"
karen  |  developer   | "B2"

- Tms91

2个回答

2

参考最新的文档，使用两个语句可以完成此操作。

添加具有旧默认值的列

ALTER TABLE my_table ADD COLUMN description varchar(100) DEFAULT 'A1';

修改列以使用不同的默认值

ALTER TABLE my_table ALTER COLUMN description SET DEFAULT 'B2'

下面是一个完整可复现的示例：

CREATE TABLE my_table (
  "name" VARCHAR(5),
  "work" VARCHAR(9)
);

INSERT INTO my_table
  ("name", "work")
VALUES
  ('bob', 'fireman'),
  ('carl', 'teacher'),
  ('alice', 'policeman');

查询 #1

select * from my_table;

名称	工作
鲍勃	消防员
卡尔	教师
爱丽丝	警察

查询 #2

ALTER TABLE my_table 
ADD COLUMN description varchar(100) 
DEFAULT 'A1';

没有可显示的结果。

查询 #3

select * from my_table;

姓名	职业	描述
鲍勃	消防员	A1
卡尔	教师	A1
爱丽丝	警察	A1

查询 #4

ALTER TABLE my_table 
ALTER COLUMN description SET DEFAULT 'B2';

没有结果可显示。

查询 #5

INSERT INTO my_table("name", "work")
VALUES ('karen', 'developer');

暂时无法显示结果。

查询 #6

select * from my_table;

姓名	工作	描述
鲍勃	消防员	A1
卡尔	教师	A1
艾丽丝	警察	A1
凯伦	开发者	B2

在 DB Fiddle 上查看演示

请告诉我这对您有用。

- ggordon

谢谢，我想问是否有一种方法可以通过一个查询同时运行“通过添加新列并将其默认设置为A1更新表”和“通过将新列默认设置为B2更新表”的命令。 - Tms91

1

@Tms91 你提出了一个好问题。然而，在最新的文档中（在本帖发布时）我没有看到这样的内容。也许他们会在未来的版本中考虑这个问题，你可以在这里提交请求或者进一步联系他们。 - ggordon

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- jjanes · Accepted Answer

是的，你可以通过在一个ALTER语句中使用两个操作来实现。

ALTER TABLE my_table 
  ADD COLUMN description varchar(100) DEFAULT 'A1', 
  ALTER COLUMN description SET DEFAULT 'B2';

我已经验证了DROP DEFAULT不能在添加列的同一命令中使用，就像SET DEFAULT NULL一样。我认为没有理由不让它起作用，只是它们的顺序不对，没有人费心强制让它按正确的顺序运行。(可能是因为没有人测试过这个确切的问题，所以没有意识到它已经损坏了)

你总是可以在不同的ALTER命令中执行它们。如果你担心有人可能会看到表处于一个中间状态，你可以在一个单独的事务中执行两个命令。然后锁定不会在中间释放，所以没有人能够看到它。将多个操作合并到一个命令中的真正优点是表不需要被多次重写。但在这里，它根本不需要被重写。