Postgres change datatype during select into statement

Question

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;

Doon · Accepted Answer · 2016-09-16 16:14:19Z

5

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;

answered Sep 16, 2016 at 16:14

Doon

20.3k3 gold badges42 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Tim Biegeleisen · Accepted Answer · 2016-09-16 16:18:11Z

3

You could also use the shorthand syntax SUM(sum_popint)::int :

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

answered Sep 16, 2016 at 16:18

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

1 Comment

Doon Over a year ago

I contemplated using the shorthand, but figured that CAST might be more useful as it ansi sql.

Collectives™ on Stack Overflow

Postgres change datatype during select into statement

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related