Adding column to table & adding data right away to

问题:

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.

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.

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)

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

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);

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...

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);   

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.

回答1:

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.

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.

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;

or, somewhat uglier:

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;

回答2:

I think you are looking for the update statement. For example:

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);

You might also consider just creating a view to do the calculation:

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