Assiging a specific data-type for temporary variable in PostgreSQL

Question

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.

saluce · Accepted Answer · 2012-11-26 18:36:07Z

4

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

edited Nov 26, 2012 at 18:36

saluce

13.4k3 gold badges52 silver badges68 bronze badges

answered Nov 26, 2012 at 18:32

A.Amidi

2,5226 gold badges28 silver badges37 bronze badges

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

Comments

Robert H · Accepted Answer · 2012-11-26 15:48:30Z

2

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.

answered Nov 26, 2012 at 15:48

Robert H

11.7k19 gold badges72 silver badges112 bronze badges

1 Comment

Ihor Romanchenko Over a year ago

Maybe max(ambtemp::int) OVER (PARTITION BY t) as max_temp ? max(text)::int and max(text::int) Can produce different results.

Collectives™ on Stack Overflow

Assiging a specific data-type for temporary variable in PostgreSQL

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related