PostgreSQL: SELECT INTO is changing a field's data type

Question

I have a table as follows:

CREATE TABLE IF NOT EXISTS foo_raw
(
    time   TIMESTAMPTZ NOT NULL,
    volume INTEGER,                  <-- note source field is of type integer
    price  DOUBLE PRECISION
);

I am populating it from a csv file:

COPY foo_raw(time,volume,price) FROM 'raw_data.csv' DELIMITER ',' CSV

I then do a SELECT INTO a new table where I am merging duplicate time rows into a total volume and volume-weighted average price.

SELECT INTO:

SELECT
    time, 
    SUM(volume)::integer AS volume,          <-- note typecast to integer here
    SUM(volume * price) / SUM(volume) AS price
INTO
    foo
FROM
    foo_raw
GROUP BY
    time
ORDER BY
    time;

If I describe my new table, I see that the volume field is of type numeric, not integer.

pg=# \d foo
                   Table "public.foo"
 Column |           Type           | Collation | Nullable | Default 
--------+--------------------------+-----------+----------+---------
 time   | timestamp with time zone |           | not null | 
 volume | numeric                  |           |          |     
 price  | double precision         |           |          |

Typecasting:

You'll note above in my SELECT INTO statement I have tried to typecast the result of SUM(volume) to integer, but that is not working either.

Question

How can I force the field to be of type integer?

Unrelated, but: The standard create table as select is preferred over the proprietary select .. into .. (but that won't solve your problem) — user330315
– user330315, Commented Aug 27, 2018 at 20:59

garysieling · Accepted Answer · 2018-08-27 19:50:37Z

3

SUM changes the data type of numbers to larger values, so it doesn't add up to a number that overflows:

bigint for smallint or int arguments, numeric for bigint arguments, otherwise the same as the argument data type

answered Aug 27, 2018 at 19:50

garysieling

3562 silver badges7 bronze badges

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

3 Comments

Steve Lorimer Over a year ago

so my typecast to integer has no effect?

garysieling Over a year ago

I tried it with the cast and both the "select into" syntax and "create table as ..." syntax, and both made a table with integer in it.

Steve Lorimer Over a year ago

That is really weird - I'm convinced I was seeing a different result...

Joe Love · Accepted Answer · 2018-08-27 19:14:50Z

1

Yes. Cast it as an integer with sum(volume)::integer like so:

 SELECT
     time, 
     SUM(volume)::integer AS volume, 
 here
     SUM(volume * price) / SUM(volume) AS price
 INTO
     foo
 FROM
     foo_raw
 GROUP BY
     time
 ORDER BY
   time;

If you're truly concerned about the data types of the result set, I'd cast every single column appropriately.

answered Aug 27, 2018 at 19:14

Joe Love

6,1262 gold badges24 silver badges33 bronze badges

1 Comment

Steve Lorimer Over a year ago

Isn't that exactly what I'm already doing, and it's not working? What am I missing?

Collectives™ on Stack Overflow

PostgreSQL: SELECT INTO is changing a field's data type

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related