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)

I'm not seeing it: select pg_typeof(count(*)) from cell_per; bigint. How are you seeing/determining it is a string? — user7070613
– user7070613, Commented Feb 26, 2021 at 16:21
@S-Man is corect. Checking the type in DataGrip it shows "count: Int". I edited the question to explain more — zumzum
– zumzum, Commented Feb 26, 2021 at 16:35
My guess is age has a declared type in the model(?) for the table, whereas count is generated and cast as a string as a default type. — user7070613
– user7070613, Commented Feb 26, 2021 at 16:43
If Postman returns that as a string in the JSON response, then this is a problem in Postman - it has nothing to do with PostgreSQL PostgreSQL most certainly does not return count(*) as a string. — user330315
– user330315, Commented Feb 26, 2021 at 16:43

Konard · Accepted Answer · 2024-08-10 18:01:14Z

18

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);

edited Aug 10, 2024 at 18:01

Konard

3,10436 silver badges28 bronze badges

answered Feb 26, 2021 at 17:07

madflow

8,6933 gold badges45 silver badges61 bronze badges

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

3 Comments

zumzum Over a year ago

very nice. I will test this out.

DJDaveMark Over a year ago

Thanks. count(*)::int was the simple solution for me.

Alper Güven Over a year ago

For those who use the latter method (BigInt), if you use the big int on an object that you want to serialize, you will get an error like this: TypeError: Do not know how to serialize a BigInt . See this for an easy solution.

S-Man · Accepted Answer · 2021-02-26 16:20:18Z

5

As the documentation states:

count ( * ) → bigint

Computes the number of input rows. Yes

count returns type bigint. Not a string type.

answered Feb 26, 2021 at 16:20

S-Man

24k9 gold badges51 silver badges78 bronze badges

Collectives™ on Stack Overflow

Postgres cast count(*) as integer?

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related