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
?