CODEWITHSUNDEEP
postgresqlintegersqldatatypes
A.Amidi
A.Amidi

Reputation: 2522

Assiging a specific data-type for temporary variable in PostgreSQL

I have a query and I need to use the result of the query in other query. So, I used "WITH .. As". The problem is that the temporary variable should be integer while it was created in text data-type automatically. How I can change or modify the code to have variable in integer data type.

WITH A as (
SELECT ambtemp,
       date_trunc('hour', dt)+
       CASE WHEN date_part('minute', dt) >= 30
            THEN interval '30 minutes'
            ELSE interval '0 minutes'
       END as t
FROM temm),

B as(
SELECT ambtemp,t,

       max(ambtemp) OVER (PARTITION BY t) as max_temp,
       min(ambtemp) OVER (PARTITION BY t) as min_temp
FROM A)
SELECT *

FROM B
WHERE (max_temp - min_temp) <= 0.2

max_temp and min_temp should be built in integer data-type while they are in text format.

Upvotes: 3

Views: 282

Answers (2)

A.Amidi
A.Amidi

Reputation: 2522

Thanks, @Igor Romanchenko

WITH A as (
SELECT ambtemp,
       date_trunc('hour', dt)+
       CASE WHEN date_part('minute', dt) >= 30
            THEN interval '30 minutes'
            ELSE interval '0 minutes'
       END as t
FROM temm),

B as(
SELECT ambtemp,t,

       max(ambtemp::float(23)) OVER (PARTITION BY t) as max_temp,
       min(ambtemp::float(23)) OVER (PARTITION BY t) as min_temp
FROM A)
SELECT *

FROM B
WHERE (max_temp - min_temp) <= 0.2

Upvotes: 4

Robert H
Robert H

Reputation: 11730

In order to change types you need to cast them using the :: operator. So to convert an integer into a string you would do myCol::Text.

In your specific case you are doing the reverse: 

 max(ambtemp) OVER (PARTITION BY t) as max_temp::int,
 min(ambtemp) OVER (PARTITION BY t) as min_temp::int

You may want to check for nulls via: 

NULLIF(your_value, '')::int

I would also be cautious of values that are not convertible to an integer.

See The PostgreSQL documentation for more info on casts and types.

Upvotes: 2

Related Questions