在PostgreSQL中向表添加列并立即向列中添加数据

Question

我正在尝试在现有表格中创建一个新的列，并使用选择语句执行除法来创建我要插入新列的数据。我写出了几个语句作为单独的查询可以工作，但我还没有能够将这些语句串联成一个单一的查询。

我仍在学习SQL，并在使用mySQL和PostgreSQL。上个月我上了一门SQL课程，现在我正在尝试做自己的项目以保持我所学技能的熟练度。

我正在对2012年明尼苏达州的选举结果进行一些工作，以便在我的表格中使用并理解我正在处理的数据。

我已经能够使用这些语句单独地修改我的表格并添加一个新的列。

ALTER TABLE mn2012ct_geom2 ADD COLUMN obama_pct decimal(10,2)
ALTER TABLE mn2012ct_geom2 ADD COLUMN romney_pct decimal(10,2)

我能够使用这个选择语句在我的终端应用中生成我想要的信息。我在这里尝试做的是从候选人得到的选票数和投票总数中创建一个十进制数。

SELECT CAST (obama AS DECIMAL) / CAST (uspres_total AS DECIMAL) 
AS obama_pct FROM mn2012ct_geom2

SELECT CAST (romney AS DECIMAL) / CAST (uspres_total AS DECIMAL) 
AS obama_pct FROM mn2012ct_geom2

现在我想将这些信息添加到我创建的新列中，可以使用类似我之前提到的Alter表语句或者如果我在此查询之前创建了该列，则可以使用插入语句。我尝试过这样的组合查询：

ALTER TABLE mn2012ct_geom2 ADD COLUMN obama_pct decimal(10,2) AS
(SELECT CAST (obama AS DECIMAL (10,2)) / CAST (uspres_total AS DECIMAL (10,2)) 
AS obama_pct FROM mn2012ct_geom2);

或者使用类似这行的插入命令而不是修改表语句

INSERT INTO mn2012ct_geom2 (romney_pct) AS
(SELECT CAST (romney AS DECIMAL (10,2)) / CAST (uspres_total AS DECIMAL (10,2)) 
AS romney_pct FROM mn2012ct_geom2);

当我尝试这样做时，它会弹出以下错误：

ERROR:  syntax error at or near "AS"
LINE 1: ...mn2012ct_geom2 ADD COLUMN obama_pct decimal(10,2) AS (SELECT...

我以为那种“Alter table”、“add column”或“insert”会起作用，因为使用相同的选择语句创建新表格时，那种格式有效。

CREATE TABLE obama_pct AS (SELECT CAST (obama AS DECIMAL (10,2)) / CAST (uspres_total     
AS DECIMAL (10,2)) AS obama_pct FROM mn2012ct_geom2);   

非常感谢您能够提供任何帮助。我一直在尝试通过谷歌和在stackoverflow上搜索来寻找答案，但似乎没有找到完全适合我正在做的事情的答案。

- Matt P

Answer 1

通常不建议在表中添加计算数据。有时需要在规范化表时这样做，但通常不需要。

如Gordon所说，在这里适当的做法是创建视图。请参阅教程。

没有ALTER TABLE ... ADD COLUMN ... AS语法。你不能随便编写句法，需要查看感兴趣的命令的文档以了解如何使用它。在psql中，\h alter table命令也很有用。

如果您确实需要根据其他列的计算结果设置新列中的值，请使用ALTER TABLE ... ADD COLUMN ... DEFAULT ...，然后在创建列之后删除DEFAULT术语。通常最好创建一个空白可空列，然后使用UPDATE语句填充它。

例如未经测试的示例：

BEGIN;

ALTER TABLE mn2012ct_geom2 ADD COLUMN obama_pct decimal(10,2);

UPDATE mn2012ct_geom2  SET romney_pct = CAST (romney AS DECIMAL (10,2)) / CAST (uspres_total AS DECIMAL (10,2);

COMMIT;

或者，有点丑陋：

BEGIN;

ALTER TABLE mn2012ct_geom2 ADD COLUMN obama_pct decimal(10,2) NOT NULL DEFAULT (CAST (obama AS DECIMAL (10,2)) / CAST (uspres_total AS DECIMAL (10,2));

ALTER TABLE mn2012ct_geom2 ALTER COLUMN obama_pct DROP DEFAULT;

COMMIT;

- Craig Ringer

---

谢谢你的建议和帮助我学习。我会好好研究一下视图并看看它们是如何工作的。 - Matt P

Answer 2

我认为你需要的是 update 语句。例如：

ALTER TABLE mn2012ct_geom2 ADD COLUMN obama_pct decimal(10,2);

update mn2012ct_geom2
    set obama_pct = CAST(obama AS DECIMAL) / CAST(uspres_total AS DECIMAL);

您可以考虑创建一个视图来进行计算：

create view v_mn2012ct_geom2 as
    select g.*, CAST(obama AS DECIMAL) / CAST(uspres_total AS DECIMAL) as mn2012ct_geom2
    from mn2012ct_geom2;

- Gordon Linoff

---

感谢您的帮助并感谢评论。在查看Craig的代码后，我发现您遗漏了设置部分，这就是为什么我一开始无法使您的答案起作用的原因。 - Matt P