Adding Column To Table & Adding Data Right Away To Column In Postgresql

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 stateme

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

ALTERTABLE mn2012ct_geom2 ADDCOLUMN obama_pct decimal(10,2);

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

COMMIT;

or, somewhat uglier:

BEGIN;

ALTERTABLE mn2012ct_geom2 ADDCOLUMN obama_pct decimal(10,2) NOTNULLDEFAULT (CAST (obama ASDECIMAL (10,2)) /CAST (uspres_total ASDECIMAL (10,2));

ALTERTABLE mn2012ct_geom2 ALTERCOLUMN obama_pct DROPDEFAULT;

COMMIT;

Solution 2:

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

ALTERTABLE mn2012ct_geom2 ADDCOLUMN obama_pct decimal(10,2);

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

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

createview v_mn2012ct_geom2 asselect g.*, CAST(obama ASDECIMAL) /CAST(uspres_total ASDECIMAL) as mn2012ct_geom2
    from mn2012ct_geom2;