Converting values from array in PostgreSQL

Question

In my PostgreSQL database I have the following schema:

CREATE TABLE slide_results (
    id integer NOT NULL,
    tags character varying[] DEFAULT '{}'::character varying[]
);

INSERT INTO slide_results (id, tags)
    VALUES (1, '{Food, Housing}');

INSERT INTO slide_results (id, tags)
    VALUES (2, '{Security, Transport}');

Now I want to have sql query that will return one additional column, this column name is domain_name. Value of this column depends on what tags column have. If tags column have Food value then domain_name should be I need food help, if tags include Transport then domain_name will have Transport help needed. So result of the query should be

id             | domain_name
1              | I need food help
2              | Transport help needed

How can I do something like this in PostgreSQL?

Here is sql fiddle with db schema:

https://www.db-fiddle.com/f/pP7ZfNzVnisSG6xCjejqkY/0

Jay Shankar Gupta · Accepted Answer · 2018-04-18 11:44:07Z

1

SELECT id,CASE WHEN 'Food' = ANY(tags) THEN 'I need food help'
               WHEN 'Transport' = ANY(tags) THEN 'Transport help needed' 
          END AS domain_name 
FROM slide_results;

answered Apr 18, 2018 at 11:44

Jay Shankar Gupta

6,1281 gold badge14 silver badges31 bronze badges

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

Comments

klin · Accepted Answer · 2018-04-18 11:52:21Z

1

It seems you need a table to convert tag to domain_name, e.g.

create table tag_domain(tag text, domain_name text);
insert into tag_domain values
('Food', 'I need food help'),
('Transport', 'Transport help needed');

and use it in this way:

select * 
from slide_results s
join tag_domain t on t.tag = any(s.tags)

Db-Fiddle.

answered Apr 18, 2018 at 11:52

klin

123k15 gold badges240 silver badges262 bronze badges

Collectives™ on Stack Overflow

Converting values from array in PostgreSQL

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related