Postgresql – How to ensure an `int8` Postgres DB column is returning that same data type

Hm, without seeing the definition of the table, it’s hard to confirm if the type actually is of type int8. I.E., if I make an assumption with a table design as follows:

CREATE TABLE MEDIA (
  emp_id SERIAL PRIMARY KEY,
  file_size int8 GENERATED ALWAYS as (emp_id * 2) STORED
);

-- insert
INSERT INTO MEDIA VALUES (1);
INSERT INTO MEDIA VALUES (2);
INSERT INTO MEDIA VALUES (3);

And then I select the values and confirm their type like such:

-- fetch 
SELECT emp_id,
pg_typeof(emp_id) as e_type,
sum(file_size) as bytes,
pg_typeof(file_size) as f_type
FROM MEDIA
group by 1

We can observe the following results:

Output:

CREATE TABLE
INSERT 0 1
INSERT 0 1
INSERT 0 1
 emp_id | e_type  | bytes | f_type 
-------+---------+-------+--------
     2 | integer |     4 | bigint
     3 | integer |     6 | bigint
     1 | integer |     2 | bigint
(3 rows)

As you can see, the type of the calculated column is indeed int8, bigint being an alias for int8.

Even if I defined a column as a string and cast it within the calculated column (which I do not recommend doing, ever.) It still returns the accurate results:

-- create
CREATE TABLE MEDIA2 (
  some_int integer,
  some_str varchar,
  file_size int8 GENERATED ALWAYS as (some_int * some_str::int8) STORED
);

-- insert
INSERT INTO MEDIA2 VALUES (1, '1');
INSERT INTO MEDIA2 VALUES (2, '2');
INSERT INTO MEDIA2 VALUES (3, '3');

-- fetch 
SELECT some_int,
pg_typeof(some_int) as i_type,
some_str,
pg_typeof(some_str) as s_type,
sum(file_size) as bytes,
pg_typeof(file_size) as f_type
FROM MEDIA2
group by some_int, some_str, file_size

 some_int | i_type  | some_str |      s_type       | bytes | f_type 
----------+---------+----------+-------------------+-------+--------
        1 | integer | 1        | character varying |     1 | bigint
        3 | integer | 3        | character varying |     9 | bigint
        2 | integer | 2        | character varying |     4 | bigint
(3 rows)

So, can you confirm how your calculated column is declared in your table definition ? Obviously this is a simple example but, I can’t reproduce your results. It could potentially be with your calculated column definition and the types it’s using.

It’s important to note that the pg_typeof() function is returning the type of the data as stored in the table. However, after summing, it is converted to type numeric as documented in Postgres Documentation sum ( bigint ) → numeric Functions Aggregate:

-- fetch 
SELECT emp_id,
pg_typeof(emp_id) as e_type,
sum(file_size) as bytes,
pg_typeof(sum(file_size)) as f_type
FROM MEDIA
group by 1;

 emp_id | e_type  | bytes | f_type  
--------+---------+-------+---------
      2 | integer |     4 | numeric
      3 | integer |     6 | numeric
      1 | integer |     2 | numeric
(3 rows)

To answer your question, which you technically already did, you can use the following methods to cast:

CAST ( value AS target_type ) or value::target_type