Postgres View: How to assign all column values to values of another column CASE WHEN?

Question

I am trying to create a view and need to assign all values of column "category" to "metric_type" but get an error.

Table from which I get data LIN_Main_Followers

metric_name metric_type          metric_value
likes       function             65
shares      country              101
likes       SIZE_10001_OR_MORE   2
likes       SIZE_11_TO_50        10
...and others

I want to create an additional column 'category' and assign 'metric_type' values to it, if it "SIZE_" like values. And assign 'company_size' value to 'metric_type' column.

My ideal result:

metric_name metric_type   metric_value  category   
likes       company_size  2             SIZE_10001_OR_MORE   
likes       company_size  10            SIZE_11_TO_50

VIEW code

SELECT test.metric_name,
       CASE WHEN test.metric_type LIKE 'SIZE_%' THEN test.category = test.metric_type ELSE test.category END,
       CASE WHEN test.metric_type LIKE 'SIZE_%' THEN  test.metric_type = 'company_size' ELSE test.metric_type END,
       test.metric_type,
       test.metric_value
FROM(SELECT  lin.metric_name,
        lin.metric_type,
        lin.metric_value,
        'Undefined'::text AS category
FROM "LIN_Main_Followers" lin
WHERE lin.metric_type LIKE 'SIZE_%') AS test

But I get an error that

ERROR: ERROR:  CASE-Types text and boolean cannot be matched
LINE 2:     CASE WHEN test.metric_type LIKE 'SIZE_%' THEN test.categ...

Gordon Linoff · Accepted Answer · 2021-09-20 11:50:33Z

1

I think you have overcomplicated the logic. You just need to select the columns you want and give them the names you want:

SELECT lin.metric_name, 'company_size' as metric_type,
       lin.metric_value,
       lin.metric_type as category
FROM "LIN_Main_Followers" lin
WHERE lin.metric_type LIKE 'SIZE_%'

answered Sep 20, 2021 at 11:50

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

Collectives™ on Stack Overflow

Postgres View: How to assign all column values to values of another column CASE WHEN?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related