[Solved] Changing datatype of a key in a object
Looking to automatically optimize YOUR SQL query? Start for free.

EverSQL Database Performance Knowledge Base

Changing datatype of a key in a object

Database type:

I have some object that was returned from my database. When i build up my table, these are the dtypes that i declared:

  1. festivalId = BIGINT
  2. performanceId = BIGINT
  3. startTime = TIME
  4. endTime = TIME

When i wanted to extract some records with a specific performanceId, my query looks something like this:

SELECT performanceId, to_char(startTime, 'HH24MI') AS startTime, to_char(endTime, 'HH24MI') AS endTime FROM Performance WHERE festivalId = $1

And the entire result returned from postgresql was concated into an object.

Right now the problem is that my javascript for my middleware is reading each performanceId that is found in each object(meaning each unique performanceId) as a string instead of a integer type. e.g. performanceId 1234567890 is printed out as "1234567890" instead of 1234567890.

Is there anyway that i can convert performanceId output as a integer?

I tried to convert it to a integer when doing my sql SELECT statement,

SELECT CAST(performanceId AS INTEGER) AS performanceId, to_char(startTime, 'HH24MI') AS startTime, to_char(endTime, 'HH24MI') AS endTime FROM Performance WHERE festivalId = $1

but i realise its different as there is a different acceptance range for INTEGER and BIGINT.

I don't know if my above post was misleading or not, so i decided to put some dummy output here:

This is the unintended output:

{
  "result": [
    {
      "performanceid": "9999999999",
      "starttime": "0900",
      "endtime": "1200"
    }
  ]
}

This is the expected output:

{
  "result": [
    {
      "performanceid": 999999999,
      "starttime": "0900",
      "endtime": "1200"
    }
  ]
}

How to optimize this SQL query?

The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:

  1. Description of the steps you can take to speed up the query.
  2. The optimal indexes for this query, which you can copy and create in your database.
  3. An automatically re-written query you can copy and execute in your database.
The optimization process and recommendations:
  1. Create Optimal Indexes (modified query below): The recommended indexes are an integral part of this optimization effort and should be created before testing the execution duration of the optimized query.
Optimal indexes for this query:
CREATE INDEX performance_idx_festivalid ON "Performance" ("festivalId");
The optimized query:
SELECT
        Performance.performanceId,
        to_char(startTime,
        'HH24MI') AS startTime,
        to_char(endTime,
        'HH24MI') AS endTime 
    FROM
        Performance 
    WHERE
        Performance.festivalId = $1

Related Articles



* original question posted on StackOverflow here.