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

问题: 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...

问题:

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?


回答1:

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


回答2:

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.

  • 发表于 2018-08-31 16:10
  • 阅读 ( 197 )
  • 分类:sof

条评论

请先 登录 后评论
不写代码的码农
小编

篇文章

作家榜 »

  1. 小编 文章
返回顶部
部分文章转自于网络,若有侵权请联系我们删除