向表&添加列立即将数据添加到PostgreSQL中的列 [英] Adding column to table & adding data right away to column in PostgreSQL
问题描述
我试图在现有表中创建新列，并使用select语句进行除法以创建要插入到新列中的数据。我已经写出的几条语句可以作为单独的查询使用，但是我无法将这些语句组合成一个查询。
I am trying to create a new column in an existing table, and using a select statement to do division to create the data I want to insert into my new column. Several of the statements I have written out will work as separate queries but I have not been able to string together the statements into one single query.
我仍在学习SQL和在MySQL和PostgreSQL中使用它。上个月，我上了SQL课程，现在我正尝试做自己的项目，以保持技能的精明。
I am still learning SQL and using it with mySQL and PostgreSQL. I took a class last month on SQL and now I am trying to do my own projects to keep my skills sharp.
我正在做一些关于2012年选举结果的工作MN可以在我的表中使用，以了解我正在使用的数据。
I am doing some work with elections results from 2012 in MN to use in my tables, to understand data I am working with.
我已经能够更改自己的表并使用这些语句单独添加一个新列
I have been able to alter my table and add a new column with these statements using them by themselves.
ALTER TABLE mn2012ct_geom2 ADD COLUMN obama_pct decimal(10,2)
ALTER TABLE mn2012ct_geom2 ADD COLUMN romney_pct decimal(10,2)
我能够使用此select语句在终端中生成所需的信息应用。我要在这里做的是根据候选人获得的总投票数创建一个十进制数字。
I was able to use this select statement produce the information I want it to in my terminal application. What I am trying to do here is create a decimal number from the number of votes that the candidate got over the total votes that was cast.
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表语句或插入语句。
Now I want to have this information adding into the new column I created either with a Alter table statement like I have above or with an insert statement if I create the column before this query.
我尝试过这样的组合查询：
I have tried a combined query like this:
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命令代替alter table语句
Or using an Insert command like this line instead of the alter table statement
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);
当我尝试这样做时，它会弹出如下错误：
When I try to do that it kicks out an error like this:
ERROR: syntax error at or near "AS"
LINE 1: ...mn2012ct_geom2 ADD COLUMN obama_pct decimal(10,2) AS (SELECT...
我认为那种类型的Alter表和add列或insert可以工作，因为这种类型的
I thought that kind of Alter table and add column or insert would work since that type of format worked when I created a new table using that same select statement.
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);
我将不胜感激，我一直在尝试通过google在stackoverflow上搜索并找到答案，但我发现的任何内容似乎都不完全符合我的意思。
Any help for you can provide I would greatly appreciate. I have been trying to google and search here on stackoverflow to find the answer but none of what I have found seems to exactly fit what I am doing it seems.
推荐答案
通常，将计算出的数据添加到表中不是一个好主意，有时您需要这样做重新标准化表时，但通常没有完成。
In general it's not a good idea to add calculated data to a table. You sometimes need to do it when re-normalizing tables, but usually it's not done.
As Gordon says, the appropriate thing to do here would be to create a view. See the tutorial.
没有 ALTER TABLE ... ADD COLUMN ... AS 。您不仅可以组成语法，还需要查看文档，以了解您感兴趣的命令以了解如何使用它。 psql 中的 \h 命令也很有用，例如 \h alter table 。
There is no ALTER TABLE ... ADD COLUMN ... AS. You can't just make up syntax, you need to look at the documentation for the command you're interested in to find out how to use it. The \h command in psql is also useful, eg \h alter table.
如果确实需要根据其他列的计算结果在新列中设置值，请使用 更改表...添加列...默认值... ，然后在创建列后 DROP DEFAULT 项。通常最好先将列创建为空白和可为空，然后用 UPDATE 语句填充。
If you do need to set values in a new column based on calculations from other columns, use ALTER TABLE ... ADD COLUMN ... DEFAULT ... then DROP the DEFAULT term after you create the column. It's often better to create the column blank and nullable, then fill it with an UPDATE statement.
例如。未经测试的示例：
E.g. untested examples:
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;
这篇关于向表&添加列立即将数据添加到PostgreSQL中的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
