ziggy ziggy - 2 months ago 20
SQL Question

Postgres change datatype during select into statement

Running postgres with postgis extension: trying to change datatype of column during a select into table statement

The column sum_popint in the munsummary table is double and I want to change it into an integer column during the select statement. I am aware I can alter the column datatype to integer before or after the select into statement using the update/alter statements but i want to do it within the select statement.

SELECT county,
SUM(sum_popint) AS residentialpopulation,
st_union(geom)
INTO countysummary
FROM munsummary
GROUP BY county;

Answer

what you are looking to do is CAST it to an integer. This takes the form of CAST ( expression AS type );

So in your SELECT, try:

SELECT county,
       CAST(SUM(sum_popint) as INTEGER) AS residentialpopulation,
       st_union(geom)
INTO countysummary
FROM munsummary
GROUP BY county;