CODEWITHSUNDEEP
postgresqlamazon-redshift
user3600910
user3600910

Reputation: 3109

How can i enter varchar values in case if the field type is integer in RedShift?

I'm trying to run this query:

select count(uid),
case when duration=0 then '0'
     when duration between 0 and 10 then '1-10 sec' end as Duration
from mr_session_log
where event_id=10     
group by
case when duration=0 then '0'
     when duration between 0 and 10 then '1-10 sec' end

But since the duration is integer Redshift doesn't allow me to enter "1-10 sec" value after the then.

Is there a way to to put different value from the original column value?

Upvotes: 0

Views: 1557

Answers (2)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656804

SQL is a strongly-typed language. @John explains the basic problem in the query nicely. But I think we need more precision how to handle data types here. I suggest:

SELECT count(uid) AS ct
     , CASE WHEN duration = 0 THEN text '0 sec' -- explicit type for string literal
            WHEN duration BETWEEN 1 AND 10 THEN '1-10 sec' -- coerced to same type
       END AS duration
FROM   mr_session_log
WHERE  event_id = 10     
GROUP  BY 2;

You can use '0 sec'::text or cast('0 sec' AS text) as well.

In modern Postgres '0 sec' or even '0' without explicit type does the same because a string literal defaults to type text in most contexts.

You could even use 0::text or cast(0 AS text), but it's pointless to provide a string as numeric literal and make Postgres cast twice (first the numeric literal is automatically cast to int in this case, then int to text).

Read the chapter about constants in the Postgres manual. (I wouldn't know of proper documentation for Redshift.)

GROUP BY 2 is just syntax shorthand referring to the 2nd SELECT item. If that should not be implemented in Redshift, repeat the CASE expression like you did before.

Upvotes: 4

John Rotenstein
John Rotenstein

Reputation: 269400

The issue that you are mixing types within a column.

  • when duration=0 then 0 returns an integer
  • when duration between 0 and 10 then '1-10 sec' returns a string
  • They are both populating the same Duration output column (which is not the same as the original duration column)

I recommend you change the first result into a string, eg: when duration=0 then '0 sec'

Upvotes: 2

Related Questions