Create database View in PostgreSQL and change record values simultaneously

Question

To cut a long story short, is it possible to create a database view in postgres and at the same time change the record values for a specific column ?

CREATE VIEW ViewOfCoupons AS
SELECT *
FROM Coupons
WHERE title = 'Category_1' OR title = 'Category_2';

I want to rename the records contained in the view to have different title other than 'Category_1' or 'Category_2', lets say 'Category_3'. In fact, as I mention in the commect, 'Category_3' is a supercategory involving the other two. So I only want the rows having these values. Is this possible with CASE statement ?

Do you want to see only rows with those categories? Or all rows and only "rename" those two categories? — user330315
– user330315, Commented Sep 28, 2016 at 11:38
Yes, I want to see only rows with those two categories. In fact, 'Category_3' is a supercategory involving those two. I will edit. Thanks. — dpesios
– dpesios, Commented Sep 28, 2016 at 11:53

score 1 · Accepted Answer · 2016-09-28 11:43:56Z

1

CREATE VIEW ViewOfCoupons AS
SELECT case title
         when 'Category_1' then 'Brand new category' 
         when 'Category_2' then 'The other category'
         else title
       end as title, 
       ... other columns ...
FROM Coupons
WHERE title in ('Category_1', 'Category_2');

Strictly speaking the else title is not necessary because the WHERE clause makes sure that no other values will appear in that column, but I add this nevertheless to be prepared for future changes, e.g. if someone changes the where clause but forgets to change the case as well.

edited Sep 28, 2016 at 11:43

answered Sep 28, 2016 at 11:36

user330315

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

4 Comments

redneb Over a year ago

I think that you don't need the WHERE clause.

user330315 Over a year ago

@redneb: well it was present in the original query. It's unclear to me if dpesios want's only rows with those values or all rows.

dpesios Over a year ago

@a_horse_with_no_name I see your point and thank you. But I want to get all the rows of Coupons (projecting all the columns) where the category is 'Category_1' or 'Category_2' and change it in the view.

dpesios Over a year ago

It's already there, the answer (... other columns ...). Thanks!

Collectives™ on Stack Overflow

Create database View in PostgreSQL and change record values simultaneously

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related