Adding column to table & adding data right away to column in PostgreSQL

alter-table, postgresql, sql, sql-insert

Solution

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;

Problem

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.

Original source