Postgres cast count(*) as integer?

Question

I have a simple query that returns count.

select count(*)
from table
wehre ....

I would like the count to return as an INTEGER and not a STRING. The default in Postgres seems to be String.

I have tried to do this:

select cast(count(*) as INTEGER)
....

but it's not working. The query runs, but the type of count is still a string. I've tried other ways unsuccessfully.

Is there a way to have the count return as an number instead of a string type?

EDIT: As I checked the type of the result table in DataGrip it does show that the count return is of type "count: Int".

What threw me off is the result as returned in Postman. The query select is this:

select person.age, count(*) from ....

When that query runs in the endpoint it's in, Postman shows this as result: [{"age":18, count: "45"}, ....]

So, then I assumed that the count form Postgres was coming back as a string because the age (INTEGER type on the table) does return back as a number type and not a string. So, now I am confused as to why one column type is right, and the other is not (count...).

The query is run using Sequelize:

const result = await sequelize.query(query, { type: sequelize.QueryTypes.SELECT });

and then return straight via:

res.status(200).json(result)

Answer 1

As of now - you already know that count returns type bigint in Postgresql.

Regarding your Sequelize question. Sequelize uses Node Postgres under the hood. Node Postgres comes with support for types.

When your are explicitly querying for person.age - Node Postgres has a supported type parser for that type of field.

For count(*) - Node Postgres will return a string. In order to cast it to (big)int - so that Javascript recognizes it - you will have to create your own Type Parser

Let's say that you know you don't and wont ever have numbers greater than int4 in your database, but you're tired of recieving results from the COUNT(*) function as strings (because that function returns int8). You would do this:

var types = require('pg').types
types.setTypeParser(types.builtins.INT8, function(val) {
  return parseInt(val, 10)
})

Or cast it like this:

SELECT count(*)::int

If you are going down the custom type parser road - since Javascript now supports Bigint and you are confident that your Node version supports it - you may aswell try:

types.setTypeParser(types.builtins.INT8, BigInt);

Answer 2

As the documentation states:

count ( * ) → bigint

Computes the number of input rows. Yes

count returns type bigint. Not a string type.